Remaining days and hours to New Year from today

This tutorial shows how to calculate the remaining days and hours from today to New Year in Excel

EXCEL FORMULA 1. Remaining days and hours to New Year from today

EXCEL

Remaining days and hours to New Year from today

=DATE(YEAR(TODAY()),12,31)-TODAY()&" days "&TEXT(TODAY()+1-NOW(),"h"" hrs""")

GENERIC FORMULA

=DATE(YEAR(TODAY()),12,31)-TODAY()&" days "&TEXT(TODAY()+1-NOW(),"h"" hrs""")

EXPLANATION

The first part of the formula is used to calculate the number of days left until the end of the year. For this it uses a combination of the DATE, YEAR and TODAY function. At the beginning of the formula we use the DATE function to return the last date for the year, with the month and day fixed at 12 months (representing the December month number) and 31 days (representing the last day in December). The current year is derived by inserting today's date, through the use of a TODAY function, inside the YEAR function to return the current year.
The formula then subtracts today's date, through the use of TODAY function. This will return the days remaining until the new year from today's date.
The later part of the formula is used to return the number of hours left in the day by using tomorrows date and time, with a time of 12:00 AM. This is achieved by using the TODAY function and adding a value of one (1) to it. The formula then subtracts the current date and time from this value, using the NOW function. This will return a value that represents the number of hours left in a day.
We then need to convert this to hours by using the TEXT function with the text format set to "h".

By combining the first part, which returns the days left until new year, and second part, which returns the hours left in a day, the formula returns the number of days and hours left until new year.

RELATED TOPICS

Related Topic Description Related Topic and Description
How to convert a date of birth to age, in years in Excel
How to calculate the remaining days from today to New Year in Excel
How to calculate the remaining days, hours and minutes from today to New Year in Excel

RELATED FUNCTIONS

Related Functions Description Related Functions and Description
The Excel DATE function returns a date through the use of individual year, month and day parameters
The Excel YEAR function returns the year from a specified date
The Excel TODAY function returns the current date
The Excel TEXT function returns a numeric value as text, in a specified format
The Excel NOW function returns the current date and time