Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate Days Left in Current Year
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate Days Left in Current Year
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 "Dave C" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate Days Left in Current Year
Dave C wrote:
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate Days Left in Current Year
Thanks Mike ... it worked a treat
"Mike H" wrote: 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 "Dave C" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculate days per year | Excel Discussion (Misc queries) | |||
Formula to calculate the current year | Excel Discussion (Misc queries) | |||
calculate prior calendar year end from a moving current date | Excel Worksheet Functions | |||
days since 1/1/current year | Excel Discussion (Misc queries) | |||
how do we calculate T-bill Price in 365 days year. | Excel Worksheet Functions |