Excel Function add year month day hour minute second to date

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)

Outbound References

http://www.excelfunctions.net/Excel-Edate.html

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist
- Microsoft Specialist
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *