Is there a formula that results in the day of the year?

January 31st is the 31st day of the year.

December 31st is the 365th in a non leap year

December 31st is the 366th in a leap year

Please advise.

Thanks, Tracey

Is there a formula that results in the day of the year?

January 31st is the 31st day of the year.

December 31st is the 365th in a non leap year

December 31st is the 366th in a leap year

Please advise.

Thanks, Tracey

```
=TODAY()-DATE(YEAR(TODAY());1;1)+1
=B2-DATE(YEAR(B2);1;1)+1
leap-year:
=DATEVALUE("2012-12-31")-DATE(2012;1;1)+1
```

```
=DATEDIF("1899-12-31";A1;"yd")
or
=A1-EOMONTH(A1;-MONTH(A1))
```

1 Like

If a tax is introduced on the length of the formula:

```
=A1-DATE(YEAR(A1);1;)
```

Try:

```
=DAYS(A1, DATE(YEAR(A1),1,1))+1
```

I just pasted your formula in and the result is 364

Today is the 46th day of the year (31+15)

I tried numerous other ways before, but could not get it to work.

Thanks, Tracey

We can only guess what your A1 containsâ€¦ If you seek help, not pity upload your sheet here, so we can check.

Develop it yourself:

- Put in A1
`=TODAY()`

- B1 set to 2024-01-01
- Format both cells as numbers, to see the dates are internally only numbers. The one in A1 should be greater than B1
- Change formatting back to date (thus changes what is shown, not the internal value).
- Write in C1 the formula
`=A1-B1`

You will realize, the shown difference is of by one from your target, as B1 is already the first day of the year. - Change the formula to
`=A1-B1+1`

What is left would be a way to do this for any year, so we need to use the year of the date in A1 instead of a fixed date in B1. This was already shown by @Lupp above with B2, wich I changed for A1

`=A1-DATE(YEAR(A1);1;1)+1`

1 Like

I ended up using: DayOfYear=TODAY()-DATE(YEAR(TODAY());1;1)+1

It appears easier to read with little explanation.

I also created the inverse: =DayOfYear+DATE(YEAR(TODAY());1;1)-1

Just FYI, Tracey