Excel Function add year month day hour minute second to date
In this tutorial, I will show how to use Excel Function to:
– add year to date
– add month to date
– add day to date
– add hour to date
– add minute to date
– add second to date
I recommend you to read my previous tutorial to understand the concept of time serial.
In the following examples, I will assume A1 contains a date 2015 Jul 1.
Add year to date
Change the Cell Format to dd/mm/yyyy
Assume that we add 5 years to 2015 Jul 1
Formula | Result |
=DATE(YEAR(A1)+5,MONTH(A1),DAY(A1)) | 01/07/2020 |
Add month to date
Change the Cell Format to dd/mm/yyyy
Assume that we add 5 months to 2015 Jul 1
Formula | Result |
=DATE(YEAR(A1),MONTH(A1)+5,DAY(A1)) | 01/12/2015 |
=EDATE(A1,5) | 01/12/2015 |
Note that EDATE is a Function to add month, the syntax of EDATE is
EDATE( start_date, months )
Function EOMONTH also add month, but it returns end date of month.
Add day to date
Change the Cell Format to dd/mm/yyyy
Assume that we add 5 days to 2015 Jul 1
Formula | Result |
=A1+5 | 06/07/2015 |
Adding date is very direct because the date serial represents number of days already.
Add hour to date
Change the Cell Format to dd/mm/yyyy h:mm:ss
Assume that we add 5 hours to 2015 Jul 1 (default time is 0:00)
Formula | Result |
=A1+5/24 | 01/07/2015 5:00:00 |
As date serial represents number of days, you need to divide additional hour by 24 (1 day = 24 hours)
Add minutes to date
Change the Cell Format to dd/mm/yyyy h:mm:ss
Assume that we add 5 mins to 2015 Jul 1 (default time is 0:00)
Formula | Result |
=A1+5/(24*60) | 01/07/2015 0:05:00 |
As date serial represents number of days, you need to divide additional minute by 24*60 (1 day = 24*60 minutes)
Add seconds to date
Change the Cell Format to dd/mm/yyyy h:mm:ss
Assume that we add 5 seconds to 2015 Jul 1 (default time is 0:00)
Formula | Result |
=A1+5/(24*60*60) | 01/07/2015 0:00:05 |
As date serial represents number of days, you need to divide additional minute by 24*60*60 (1 day = 24*60*60 seconds)
Add day to date