Forum Discussion
Add 1 Year to the Second to a Date
Hi! I have a need to add 1 year to the second to a date in Excel. For example:
Start: 3/16/2026 4:06:28 PM MST
End: 3/16/2027 4:06:28 PM MST
I know that EDate can be used to easily add 1 year to the date portion of the datetime value, but that zeros out the time portion of the value. I'm thinking that I could extract the time portion of the starting datetime value, use EDate to add 1 year to the date portion, and then add the time portion back on. My Excel skills are a bit rusty, but I could probably figure that out. That being said, I'm hoping there is an easier way, like maybe a function that I'm not aware of that can handle it easily. I would prefer to do this without needing macros.
What's the best way to add 1 year to the second to a datetime value in Excel?
Thanks in advance for any help that you can offer!
--Tom
2 Replies
- Olufemi7Iron Contributor
Hellomtthomasm516,
Use the formula =EDATE(A1,12)+MOD(A1,1) where A1 contains the original datetime. This adds 12 months to the date portion and preserves the time portion exactly.
- Detlef_LewinSilver Contributor
=EDATE(A1,12)+MOD(A1,1)