Remaining days and hours to New Year from a specific date and time
This tutorial shows how to calculate the remaining days and hours from a specific date and time to New Year in Excel
|
GENERIC FORMULA
=ROUNDDOWN(DATE(YEAR(date),12,31)+1-date,0)&" days "&24-TEXT(date,"h")&" hrs"
ARGUMENTS 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 ROUNDDOWN, DATE and YEAR 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 year of the selected date is derived with the use of the YEAR function.
The formula then subtracts the specified date and then rounds down this result to return the number days remaining until the new year from specified date.
The later part of the formula is used to return the number of hours left in the day by returning the number of hours that have already elapsed in the specified date and time using the TEXT function with the text format of "h". Given there are 24 hours in a day, this hours that have elapsed is then subtracted from 24 hours to return the number of hours that is left in a day.
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 from the specified date and time. |
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Remaining days to New Year from a specific date | How to convert a date of birth to age, in years in Excel | |
| Remaining days to New Year from today | How to calculate the remaining days from today to New Year in Excel | |
| Remaining days, hours and minutes to New Year from today | 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 |
|---|---|---|
| DATE Function | The Excel DATE function returns a date through the use of individual year, month and day parameters | |
| YEAR Function | The Excel YEAR function returns the year from a specified date | |
| ROUNDDOWN Function | The Excel ROUNDDOWN function returns a number that is rounded down in accordance with the specified number of digits | |
| TEXT Function | The Excel TEXT function returns a numeric value as text, in a specified format |