Calculate Days Left in Current Year

[Dave C]

I need to perform a calculation that returns the number of days that remain
in the current year from a variable date entered into a cell. I could enter
the last day of the year - 31/12/XX - into another cell but this would
require the user to update that value when the year changed. Can I base the
value of the (end of year) cell on a formula so the year changes
automatically when 31 Dec becomes 1 Jan - ie: change from 31/12/08 to
31/12/09 - or is there a formula where I can obtain the number of days
remaining in the current year based on the value entered into the variable
cell?

Thanks for any suggestions /solutions

Dave

 

[Mike H]

Hi,

For a running total use

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

or for a date in a cell use

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

Where a1 is a date

Mike

 

[Glenn]

I need to perform a calculation that returns the number of days that remain
in the current year from a variable date entered into a cell. I could enter
the last day of the year - 31/12/XX - into another cell but this would
require the user to update that value when the year changed. Can I base the
value of the (end of year) cell on a formula so the year changes
automatically when 31 Dec becomes 1 Jan - ie: change from 31/12/08 to
31/12/09 - or is there a formula where I can obtain the number of days
remaining in the current year based on the value entered into the variable
cell?

Thanks for any suggestions /solutions

Dave

With your "variable date" entered in A1:

=DATE(YEAR(A1)+1,1,1)-A1

So, for December 31st, the result would be 1. If you want the result to be 0,
change the formula to the following:

=DATE(YEAR(A1)+1,1,0)-A1

 

[Dave C]

Thanks Mike ... it worked a treat

Hi,

For a running total use

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

or for a date in a cell use

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

Where a1 is a date

Mike