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 business analysis, project management, and also 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 in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

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