# MS-Excel question: function for elapsed time? **URL:** \ **Category:** Factual Questions\ **Created:** [August 7, 2017, 4:13pm UTC](https://boards.straightdope.com/t/ms-excel-question-function-for-elapsed-time/793009 "2017-08-07T16:13:56Z")\ **Posts on this page:** 19\ **Page:** 1
**Author:** ![Northern\_Piper](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/northern_piper/32/5304_2.png) [@Northern\_Piper](https://boards.straightdope.com/u/Northern_Piper)\ **Post date:** [August 7, 2017, 4:13pm UTC](https://boards.straightdope.com/t/ms-excel-question-function-for-elapsed-time/793009/1 "2017-08-07T16:13:56Z")
I’m working on some historical data in an Excel spreadsheet and have a question. Is there a function where I can enter a date, and Excel automatically calculates how much time has elapsed since that date? So if I put in June 1, 2010, it will tell me how many days, months, years ago that was? ---
**Author:** ![Crotalus](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/crotalus/32/41_2.png) [@Crotalus](https://boards.straightdope.com/u/Crotalus)\ **Post date:** [August 7, 2017, 4:22pm UTC](https://boards.straightdope.com/t/ms-excel-question-function-for-elapsed-time/793009/2 "2017-08-07T16:22:06Z")
If you have a cell that contains the function =NOW(), let’s say that is in A1, and another cell, B1, that contains a date, and a third cell that contains =A1 - B1, that will return the elapsed time between the two dates in days. ---
**Author:** ![jonesj2205](https://avatars.discourse-cdn.com/v4/letter/j/ecc23a/32.png) [@jonesj2205](https://boards.straightdope.com/u/jonesj2205)\ **Post date:** [August 7, 2017, 4:24pm UTC](https://boards.straightdope.com/t/ms-excel-question-function-for-elapsed-time/793009/3 "2017-08-07T16:24:10Z")
If you use the function Now() Excel will tell you the current date and time. If A1 has the date you want to check: =Now()-A1 Then format it to display how you want. Edged out by Crotalus ---
**Author:** ![Crotalus](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/crotalus/32/41_2.png) [@Crotalus](https://boards.straightdope.com/u/Crotalus)\ **Post date:** [August 7, 2017, 6:59pm UTC](https://boards.straightdope.com/t/ms-excel-question-function-for-elapsed-time/793009/4 "2017-08-07T18:59:14Z")
> [@jonesj2205](#): > > If you use the function Now() Excel will tell you the current date and time. If A1 has the date you want to check: > =Now()-A1 > Then format it to display how you want. > > Edged out by Crotalus I think this might be the first time in over ten years I have been the ninja, rather than the ninja’d. Thanks for typing slowly. 🙂 ---
**Author:** ![markn\_1](https://avatars.discourse-cdn.com/v4/letter/m/f9ae1b/32.png) [@markn\_1](https://boards.straightdope.com/u/markn_1)\ **Post date:** [August 7, 2017, 7:14pm UTC](https://boards.straightdope.com/t/ms-excel-question-function-for-elapsed-time/793009/5 "2017-08-07T19:14:35Z")
The above answers will give you the number of days since a given date. The OP asked for “days, months, years”, but that’s not well defined since different months are not the same length, and similarly different (calendar) years are not the same length. So if the OP really wants months and years, they need to clarify what they mean by that. ---
**Author:** ![Whack-a-Mole](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/whack-a-mole/32/141_2.png) [@Whack-a-Mole](https://boards.straightdope.com/u/Whack-a-Mole)\ **Post date:** [August 7, 2017, 7:37pm UTC](https://boards.straightdope.com/t/ms-excel-question-function-for-elapsed-time/793009/6 "2017-08-07T19:37:10Z")
Assuming: Start Date: A1 End Date: B1 Difference Result: C1 (Format A1 and B1 as dates) Then in C1 put: =DATEDIF(A1,B1,“y”) &" years,"&DATEDIF(A1,B1,“ym”) &" months," &DATEDIF(A1,B1,“md”) &" days" ---
**Author:** ![LSLGuy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lslguy/32/5813_2.png) [@LSLGuy](https://boards.straightdope.com/u/LSLGuy)\ **Post date:** [August 7, 2017, 11:19pm UTC](https://boards.straightdope.com/t/ms-excel-question-function-for-elapsed-time/793009/7 "2017-08-07T23:19:59Z")
Dates and times in Excel confuse a lot of people because what’s going on under the hood is not obvious. And the various formatting options further mask the difference between the \*value \*Excel is actually dealing with versus what you see _displayed_ on the screen in the cell. I would never use NOW() to compare with a date. Use TODAY() instead. Other than that quibble the earlier advice is good as far as it goes. If you (OP) want to understand, instead of just copying recipes blindly, let us know. Somebody will be happy to oblige. ---
**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\ **Post date:** [August 8, 2017, 12:08am UTC](https://boards.straightdope.com/t/ms-excel-question-function-for-elapsed-time/793009/8 "2017-08-08T00:08:51Z")
> [@LSLGuy](#): > > I would never use NOW() to compare with a date. Use TODAY() instead. Why not? TODAY is all that’s necessary but I cannot think of a situation where NOW would give a wrong answer or be a disadvantage in any other way. ---
**Author:** ![Northern\_Piper](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/northern_piper/32/5304_2.png) [@Northern\_Piper](https://boards.straightdope.com/u/Northern_Piper)\ **Post date:** [August 8, 2017, 12:14am UTC](https://boards.straightdope.com/t/ms-excel-question-function-for-elapsed-time/793009/9 "2017-08-08T00:14:56Z")
> [@markn\_1](#): > > The above answers will give you the number of days since a given date. The OP asked for “days, months, years”, but that’s not well defined since different months are not the same length, and similarly different (calendar) years are not the same length. So if the OP really wants months and years, they need to clarify what they mean by that. Thinking it over, I don’t need that precision I suggested in my OP, just the value in years. Once I do the TODAY subtract the starting year, that gives me the number of elapsed days, correct? So I could just do another operation, where the number of elapsed days is divided by 365.25, round it to two decimal places, and that gives me the number I need. (My data table has approx. 200 dates in it, and I don’t need exact precision (Years, months, days) for every single one.) ---
**Author:** ![Northern\_Piper](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/northern_piper/32/5304_2.png) [@Northern\_Piper](https://boards.straightdope.com/u/Northern_Piper)\ **Post date:** [August 8, 2017, 12:18am UTC](https://boards.straightdope.com/t/ms-excel-question-function-for-elapsed-time/793009/10 "2017-08-08T00:18:53Z")
> [@LSLGuy](#): > > If you (OP) want to understand, instead of just copying recipes blindly, let us know. Somebody will be happy to oblige. Thanks, and thanks to all for the comments so far. I am a techno-peasant and barely understand some of the answers, so following recipes is about my speed! I’ll try tinkering with my spreadsheet and will come back with any (inevitable) further questions. 🙂 ---
**Author:** ![Northern\_Piper](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/northern_piper/32/5304_2.png) [@Northern\_Piper](https://boards.straightdope.com/u/Northern_Piper)\ **Post date:** [August 8, 2017, 12:20am UTC](https://boards.straightdope.com/t/ms-excel-question-function-for-elapsed-time/793009/11 "2017-08-08T00:20:34Z")
> [@CookingWithGas](#): > > Why not? TODAY is all that’s necessary but I cannot think of a situation where NOW would give a wrong answer or be a disadvantage in any other way. Yes, what’s the difference, and why is TODAY better than NOW? ---
**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\ **Post date:** [August 8, 2017, 12:22am UTC](https://boards.straightdope.com/t/ms-excel-question-function-for-elapsed-time/793009/12 "2017-08-08T00:22:29Z")
Dates in Excel are stored as the decimal number of days since 12/31/1899, where 0:00 on 1/1/1900 = 1. The decimal part is fractions of a day, so for example 1/24 = 1 hour. You can do date arithmetic with dates in Excel, but if you try to display a negative number as a date Excel will barf and your cell will fill with “#” signs. In your case, the advice above is to use the TODAY function, which will give the date value for the current date. If you subtract your other date from TODAY, the result is a decimal number that gives the number of days elapsed. You can use various functions to display that difference in days/months/years. Here is one option: =TEXT(TODAY()-A1,“d ““days”” m ““months”” y ““years”””) DATEDIF referenced in a post above also works although it is included for compatibility with old Lotus 1-2-3 files and does seem to have a [known issue](https://support.office.com/en-us/article/DATEDIF-function-25DBA1A4-2812-480B-84DD-8B32A451B35C?NS=EXCEL&Version=16&SysLcid=1033&UiLcid=1033&AppVer=ZXL160&HelpId=xlmain11.chm60399&ui=en-US&rs=en-US&ad=US). ---
**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\ **Post date:** [August 8, 2017, 12:25am UTC](https://boards.straightdope.com/t/ms-excel-question-function-for-elapsed-time/793009/13 "2017-08-08T00:25:45Z")
> [@Northern\_Piper](#): > > So I could just do another operation, where the number of elapsed days is divided by 365.25, round it to two decimal places, and that gives me the number I need. You don’t need to do all that; you can let Excel do it for you: =TEXT(TODAY()-A1,“y ““years”””) > [@Northern\_Piper](#): > > Yes, what’s the difference, and why is TODAY better than NOW? The difference is that TODAY gives only the integer portion which will only tell you what day it is, and NOW gives the full decimal version which also tells you what time it is. ---
**Author:** ![Northern\_Piper](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/northern_piper/32/5304_2.png) [@Northern\_Piper](https://boards.straightdope.com/u/Northern_Piper)\ **Post date:** [August 8, 2017, 12:29am UTC](https://boards.straightdope.com/t/ms-excel-question-function-for-elapsed-time/793009/14 "2017-08-08T00:29:33Z")
> [@CookingWithGas](#): > > Dates in Excel are stored as the decimal number of days since 12/31/1899, where 0:00 on 1/1/1900 = 1. The decimal part is fractions of a day, so for example 1/24 = 1 hour. Oh dear. Several of my dates are older than that, starting in the 17th century. I may have to wrangle those ones by hand. > [@](#): > > You can do date arithmetic with dates in Excel, but if you try to display a negative number as a date Excel will barf and your cell will fill with “#” signs. > > In your case, the advice above is to use the TODAY function, which will give the date value for the current date. If you subtract your other date from TODAY, the result is a decimal number that gives the number of days elapsed. You can use various functions to display that difference in days/months/years. Here is one option: > > =TEXT(TODAY()-A1,“d ““days”” m ““months”” y ““years”””) > > DATEDIF referenced in a post above also works although it is included for compatibility with old Lotus 1-2-3 files and does seem to have a [known issue](https://support.office.com/en-us/article/DATEDIF-function-25DBA1A4-2812-480B-84DD-8B32A451B35C?NS=EXCEL&Version=16&SysLcid=1033&UiLcid=1033&AppVer=ZXL160&HelpId=xlmain11.chm60399&ui=en-US&rs=en-US&ad=US). Thanks! ---
**Author:** ![Whack-a-Mole](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/whack-a-mole/32/141_2.png) [@Whack-a-Mole](https://boards.straightdope.com/u/Whack-a-Mole)\ **Post date:** [August 8, 2017, 12:57am UTC](https://boards.straightdope.com/t/ms-excel-question-function-for-elapsed-time/793009/15 "2017-08-08T00:57:06Z")
> [@Northern\_Piper](#): > > Oh dear. Several of my dates are older than that, starting in the 17th century. I may have to wrangle those ones by hand. > > Thanks! The answer I gave you in Post #6 does exactly what you want it to _except_ it will not calculate on dates before 1900. Here’s a pic of it working on my PC: [Imgur: The magic of the Internet](https://i.imgur.com/kO17wna.png) (NOTE: I chose that formatting just cuz…any of the date formats will work.) ---
**Author:** ![DPRK](https://avatars.discourse-cdn.com/v4/letter/d/4491bb/32.png) [@DPRK](https://boards.straightdope.com/u/DPRK)\ **Post date:** [August 8, 2017, 1:06am UTC](https://boards.straightdope.com/t/ms-excel-question-function-for-elapsed-time/793009/16 "2017-08-08T01:06:39Z")
Go [here](https://en.wikipedia.org/wiki/Julian_day#Converting_Julian_or_Gregorian_calendar_date_to_Julian_day_number) and scroll down to the section on “Converting… calendar date to Julian day number.” There is a formula there that outputs the day number given the year, month, and day, even if the year is in the 17th century. Then you can subtract. ---
**Author:** ![LSLGuy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lslguy/32/5813_2.png) [@LSLGuy](https://boards.straightdope.com/u/LSLGuy)\ **Post date:** [August 8, 2017, 2:34am UTC](https://boards.straightdope.com/t/ms-excel-question-function-for-elapsed-time/793009/17 "2017-08-08T02:34:15Z")
> [@CookingWithGas](#): > > Why not? TODAY is all that’s necessary but I cannot think of a situation where NOW would give a wrong answer or be a disadvantage in any other way. Once it gets to be after 12:00 noon on any day, the fractional part of any subtraction or addition involving a pure date (i.e. an integer) and NOW() will have a fractional part \> 0.5. Now introduce some rounding in further calculations and you’re off by a day. Oops. Given how little people understand the fundamentals of how dates, times, and datetimes are stored and formatted, randomly throwing ever-changing fractions between 0.00000001 and 0.9999999 into the stew is just asking for errors. That’s why you use TODAY() instead of NOW() when your values are pure dates. ---
**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\ **Post date:** [August 8, 2017, 12:58pm UTC](https://boards.straightdope.com/t/ms-excel-question-function-for-elapsed-time/793009/18 "2017-08-08T12:58:19Z")
> [@Northern\_Piper](#): > > Oh dear. Several of my dates are older than that, starting in the 17th century. I’ll never understand why Excel persists in believing that time started in 1900. ---
**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\ **Post date:** [August 8, 2017, 1:00pm UTC](https://boards.straightdope.com/t/ms-excel-question-function-for-elapsed-time/793009/19 "2017-08-08T13:00:33Z")
> [@LSLGuy](#): > > […]will have a fractional part \> 0.5. Now introduce some rounding in further calculations and you’re off by a day. Oops. Point taken. I guess I have never seen this issue because I have never rounded off such a result but I would agree this is safer.