1

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.

3
  • This will help you support.microsoft.com/kb/214094
    – Wutnaut
    May 29, 2014 at 18:51
  • 1
    =day360() will find the number of days between 2 inputted dates.
    – user304064
    May 29, 2014 at 18:52
  • 1
    @bobSmith1432 Yes, that seems to asnwer this post. Why not write a full answer instead of a comment?
    – LPChip
    May 29, 2014 at 19:04

2 Answers 2

4

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.

0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .