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.