**QUESTION :**

I am trying to show the date in an adjacent cell as the number of days in the year.

Jan 1 would be 1, Feb 1 would be 32, Dec 31 would be 365.

Also the reverse, how many days are left in the year.

Jan 1 would be 364, Feb 1 would be 333, Dec 30 would be 1.

**ANSWER :**

Subtraction of dates in Excel returns the difference between the dates in days. Use the following to find the day # in the year for a date in `A1`

:

```
=VALUE(A1-DATEVALUE("Jan 1")+1)
```

To find the number of days left in the year, use the following related formula:

```
=VALUE(DATEVALUE("Dec 31")-A1)
```

The `VALUE`

functions are used to avoid having to apply a number format to the cell. If not used, Excel will format the result as a date.

Exploit the fact that `VALUE()`

returns an integer giving the number of days since the 1900 epoch, to subtract the epoch of the first day of previous year from the epoch of the desired date:

e.g. with `TODAY()`

as the date:

`=VALUE(TODAY())-VALUE(EOMONTH(TODAY(), -MONTH(TODAY())))`

If today is 2015-11-10, you get `314`

; if today is 2015-01-01, you get `1`

, etc.