Number of Days Left In Year w/Auto Year Change

Number of Days Left In Year w/Auto Year Change

Post by prsr » Thu, 15 Mar 2007 22:53:07


I need to create a formula that would display the days left in the
current year. However I need the year to change automatically from
year-to-year.

I have a formula to display the year.
I put 1/1/0000 in A1 and this forumla in whatever cell I need it to
display the year:

=IF(ISTEXT(A1),YEAR(TODAY())-RIGHT(A1,4),(TODAY()-A1)/365)
----------
I have a formula to display the number of days until December 31:

=DATEVALUE("12-December-2007")-TODAY()&""

Combining these two formulas does not work.

Any assistance would be appreciated.
 
 
 

Number of Days Left In Year w/Auto Year Change

Post by blheem » Fri, 16 Mar 2007 07:41:57

> I need to create a formula that would display the days left in the

There are probably a 100 ways to work this problem. You can use the
Date funciton to determine the serial date for Dec. 31 of the current
year this way..

=DATE(YEAR(TODAY()),12,31)

Then just subtract the serial value for today off of this and display
as a numeric value.

=DATE(YEAR(TODAY()),12,31)-TODAY()

Note that you will have to change the display format from date/time to
numeric. If you don't do this the result (for today March 14, 2007)
will display as 10/18/1900 (Oct. 18, 1900). This is because Oct. 18
is the 292nd day after 1/1/1900 when the serial dates start and there
are 292 days left in the current year.

Hope that helps,
Brian

 
 
 

Number of Days Left In Year w/Auto Year Change

Post by prsr » Fri, 16 Mar 2007 09:08:29

Thanks Brian... I can now finish me spreadsheet and have ye master
proud.

Good Pattys Day To Ya'