Here, we will learn how to use the Excel date functions: TODAY, NOW, DATE, DAY, MONTH and YEAR. Later, we will also learn how to return the first day of the current year & month and the first day of the current year.
Excel TODAY function
The Excel TODAY function returns the current date that is updated automatically when the worksheet is changed or opened. The function takes no arguments. For example, to return the current date, use TODAY like this: =TODAY(). To return the last week date, use TODAY like this: =TODAY()-7. To return the next week date, use TODAY like this: =TODAY()+7.
Excel NOW function
The Excel NOW function returns the current date and time that is updated automatically when the worksheet is changed or opened. The function takes no arguments. For example, to return the current date and time, use NOW like this: =NOW(). To return the last week date and current time, use NOW like this: =NOW()-7. To return the next week date and current time, use NOW like this: =NOW()+7.
Excel DATE function
The Excel DATE function creates date from the given individual year, month and day components. The formula in J4, copied down, is:
=DATE(G4,H4,I4)
Excel DAY function
The Excel DAY function returns the day number based on the given date. The formula in M4, copied down, is:
=DAY(L4)
Excel MONTH function
The Excel MONTH function returns the month number based on the given date. The formula in P4, copied down, is:
=MONTH(O4)
Excel YEAR function
The Excel YEAR function returns the 4-digit year number based on the given date. The formula in S4, copied down, is:
=YEAR(R4)
Returning the first day of the current year & month
You can return the first day of the current year & month using the formula based on the TODAY, YEAR, DATE and MONTH functions. The formula in A11, is:
=DATE(YEAR(TODAY()),MONTH(TODAY()),1)
How this formula works
The first expression DATE(YEAR(TODAY()) is evaluated. TODAY will return the current date, like 17-Aug-2024. YEAR will extract the year from this date and return it as the 4-digit year number, like 2024. The second expression MONTH(TODAY()) is evaluated. TODAY will return the current date, like 17-Aug-2024. MONTH will extract the month from this date and return it as the month number, like 8. Now the formula becomes like this: =DATE(2024,8,1). Finally, DATE creates the valid date. Therefore, we get the result as 01-08-2024.
Returning the first day of the current year
You can return the first day of the current year using the formula based on the TODAY, YEAR and DATE. The formula in A14, is:
=DATE(YEAR(TODAY()),1,1)
How this formula works
TODAY will return the current date, like 17-Aug-2024. YEAR will extract the year from this date and return it as the 4-digit year number, like 2024. Now the formula becomes like this: =DATE(2024,1,1). Finally, DATE creates the valid date. Therefore, we get the result as 01-01-2024.
You have successfully learnt how to use the Excel date functions: TODAY, NOW, DATE, DAY, MONTH and YEAR. You have also learnt how to return the first day of the current year & month and the first day of the current year! I hope this post helped you.
