42 tips for Excel time travelers

Posted on October 17th, 2013 in Excel Howtos - 50 comments

Excel Date & Time tipsToday, let’s travel in time.  Pack your photon ray guns, extra underwear, buckle your seat belts and open Excel!

Of course, we are not going to travel in time. (Come to think of it, we are going to travel in time. By the time you finish reading this, you would have traveled a few minutes)

We are going to learn how to travel in time when using Excel. In simple terms, you are going to learn how to move forward or backward in time using Excel formulas.

So are you ready to hit the warp speed? Let’s beam up our Excel time machine.

Tip 0 – Date & Time are an illusion

Most important tip for Excel time travelers is to understand that Excel dates & times are just numbers. So when you see a date like 17-October-2013 in a cell, you can safely assume that it is a number disguised to look like 17th of October, 2013. To see the number behind this, just select the cell and format it as number (from Home ribbon).

Date & Time values are numbers in Excel

Now that you understood this concept, let’s jump in to the 42 tips. All these tips assume a date or time value is in the cell A1.

Staying at present:

  1. To have latest star date in a cell, just press CTRL+; (of course, in Excel world, star date is nothing but whatever date your computer shows)
  2. To have current time in a cell, just press CTRL+:
  3. Of course, we time travelers are lazy. So pressing CTRL+; every day or CTRL+: every second is not cool. That is why you can use =TODAY() in a cell to get today’s date. It will automatically change when you re-open the file tomorrow.
  4. Likewise, use =NOW() to get current date & time in a cell. Remember, although time changes every second, you will not see the cell updated unless the formula is somehow re-calculated. This is done by,
    • Pressing F9
    • Saving / re-opening the file
    • Making any changes to any cell (like typing a value, changing a value)
    • Editing the formula cell and pressing Enter
  5. To check if today is after or before the date in cell A1, you can use =TODAY() > A1. This will be TRUE if A1 has a past date and FALSE if A1 has a future date.
  6. To know how many days are there between TODAY and the date in A1, use =TODAY() – A1. This will be a negative number if A1 is a future date. To see just the number of days (without negative sign), you can use =ABS(TODAY()-A1)
  7. To know how many hours are left between the time in A1 and current time, use =(NOW()-A1)*24.
  8. While the above formula works, it shows hours and fraction. To just see hours and minutes left, you can use =TEXT((NOW()-A1), “[hh]:mm”). Note: This formula works only when A1 < NOW().
  9. To know how many weeks are left between TODAY() date and a future date in A1, use =(TODAY() -
    A1)/7
  10. To know how many months are left between TODAY() and date in A1, use = DATEDIF(TODAY(), A1, “m”).
    Related: How to use DATEDIF function.
  11. To know which month is running, use =MONTH(TODAY())
  12. To see the month name instead of number, use =TEXT(TODAY(), “MMMM”). This shows the month’s name in your Excel language.
  13. To know which year is running, use =YEAR(TODAY())
  14. To see the last 2 digits of the year, you can use =RIGHT(YEAR(TODAY()), 2)
  15. To find the day of week for TODAY, use =WEEKDAY(TODAY()). This will give a number (1 to 7, 1 for Sunday, 7 for Saturday).
  16. To see the weekday name instead of number, use =TEXT(TODAY(), “DDDD”).
  17. To see today’s date alone, use =DAY(TODAY())
  18. To know if the present year is a leap year or not, see this.

Going back in time

  1. To go back by 6 days from the date in A1, use =A1-6
  2. To go back to last Friday use =A1-WEEKDAY(A1, 16). This works in Excel 2010, 2013. If your time machine is old (ie you have Excel 2003 or earlier versions), you can use =A1-CHOOSE(WEEKDAY(A1), 2,3,4,5,6,7,1)
  3. To go back by 5 weeks, use =A1-5*7
  4. To go back to start of the month, use =DATE(YEAR(A1), MONTH(A1),1)
  5. To go back to end of previous month, use = DATE(YEAR(A1), MONTH(A1),1) – 1
  6. Or use =EOMONTH(A1,-1)
  7. To go back by 2 months, use =EDATE(A1, -2)
  8. To go back by 27 working days, use =WORKDAY(A1, -27). This assumes, Monday to Friday as working days.
  9. To go back by 27 working days, assuming you follow Monday to Friday work week and a set of extra holidays, use =WORKDAY(A1, -27, LIST_OF_HOLIDAYS)
  10. To go back by 7 quarters, use =EDATE(A1, -7 * 3)
  11. To go back to the start of the year, =DATE(YEAR(A1), 1,1)
  12. To go back to same date last year, = DATE(YEAR(A1)-1, MONTH(A1), DAY(A1))
  13. To go back a decade, =DATE(YEAR(A1)-10, MONTH(A1), DAY(A1))

Going forward in time

We, time travelers are smart people. Once you know that turning the knob backwards takes you to past, you know how to go to future. So I am giving very few examples for going forward in time.

  1. To go to the 17th working day from date A1, assuming you use Sunday to Thursday workweek, use =WORKDAY.INTL(A1,17,7). This formula works in Excel 2010 or above.
  2. To go to next hour, use=A1+1/24
  3. To go to next day morning 9AM, use =INT(A1+1) + 9/24
  4. To go to 18th of next month, use =DATE(YEAR(A1), MONTH(A1)+1, 18)
  5. To go to end of the current quarter for date in A1, use =DATE(YEAR(A1), CHOOSE(MONTH(A1), 4,4,4,7,7,7,10,10,10,13,13,13),1)-1
  6. To go to a future date that is 4 years, 6 months, 7 days away from A1, use =DATE(YEAR(A1)+4, MONTH(A1)+6, DAY(A1)+7)

Finding the amount of time traveled

  1. To know how many days are between 2 dates (in A1 & A2), use =A1-A2
  2. To know how many working days are between 2 dates, use =NETWORKDAYS(A1, A2) (remember: A1 should be less than A2).

Fixes for common time travel hiccups

  1. If you see ###### instead of a date in a cell, try making the column wider. If you still see ######, that means the date value is not understandable by Excel (negative numbers, dates prior to 1st of January 1900 etc.)
  2. Often when pasting date values in to Excel, you notice that they are not treated as dates. Use these techniques to fix.
  3. If you pass in-correct values or use wrong parameters, your date formulas show an error like #NUM or #VALUE. Read this to understand how to fix such errors.

Quiz time for time travelers

I see that you safely made it here. I hope you had a good journey. Let me see how good your time traveling is. Answer these questions:

  • Write a formula to take date in A1 to next month’s first Monday.
  • Given a date in A1, find out the closest Christmas date to it.

Building your own time machine? Check out these tips too

If you work with date & time values often, then learning about them certainly pays off. Read below articles to one up your time travel awesomeness.

Good luck time traveling. I will see you again in future :)

PS: Make sure you attempt the challenges and post your answers in comments.

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

50 Responses to “42 tips for Excel time travelers”

  1. Rob T says:

    I’m confused by some of the tips above:

    #7: Subtracting dates/times gives an answer in days, so shouldn’t we multiply by 24, not divide? (should be =(NOW()-A1)*24)

    #8: If we’re formatting the result as a time in hrs & minutes, it isn’t necessary to do the (incorrect) calculation to hours first (should be =TEXT((NOW()-A1), “[hh]:mm”)

    I’m still working through the list, so will post again if I spot anything else.

    • Chandoo says:

      @Rob… Thanks for your comment. I have fixed #7. #8 is correct. Test it in Excel to see how it works.

      • Rob T says:

        I still don’t agree on #8.

        With my A1 as 17/10/13 9:00, and the current time from =NOW() being 17/10/13 12:06:

        =TEXT((NOW()-A1)/24,”[hh]:mm”)
        gives 00:07 (incorrect)

        =TEXT((NOW()-A1), “[hh]:mm”)
        gives 03:06 (correct)

        • Chandoo says:

          You are right. I had the correct version of formula =TEXT((NOW()-A1), “[hh]:mm”) in my testing workbook. But the post had the wrong one. Fixed it too. Thank you so much for correcting me :)

  2. Rob T says:

    With regard to your challenges, my solution for the date of the next month’s first Monday is:

    =EOMONTH(A1,0)+7-WEEKDAY(EOMONTH(A1,0),3)

    I’m still struggling with the xmas one. I’ve got something that works but is hideously (and embarassingly) long, so I’m not prepared to post it!

  3. Rich says:

    Rob, try using an =IF() statement on A1 and use some variation of YEAR() within it.

    Chandoo – what program did you use to make the GIF image?

  4. Rob T says:

    I was trying to come up with a way to avoid repeating the two DATE() functions, but all my efforts just made the formula longer than having the repeat. Here’s what I settled on:

    =IF(DATE(YEAR(A1),12,25)-A1>A1-DATE(YEAR(A1)-1,12,25),DATE(YEAR(A1)-1,12,25),DATE(YEAR(A1),12,25))

    I’m not sure what the required response was for dates like 25-Jun-2012, where the difference would be 183 days in both directions, but this formula just gives the one in the same year.

  5. Les says:

    What tool (application) do you use to post the Excel animations?

  6. J Gutz says:

    Chandoo trainee who has been enjoying learning from the sidelines but thought I would try one of these challenges. Might not be the most elegant or short but I think it works.

    =IF(WEEKDAY(DATE(YEAR(A3),MONTH(A3)+1,1),3)=0,DATE(YEAR(A3),MONTH(A3)+1,1),DATE(YEAR(A3),MONTH(A3)+1,1)+7-WEEKDAY(DATE(YEAR(A3),MONTH(A3)+1,1),3))

    Thanks to all for a great location to learn and try out new things as I become amazing at excel

  7. Samuel Cruz says:

    Hello to all. My name is Samuel Cruz from Matomoros, Mexico.
    I am relatively new in Chandoo Site and I had seen many interesting things. Like you all, I want to become awesome in excel.

    Because of my work, I have to deal with dates in Excel (and Yes, I do some time traveling), and after some time of testing and failures a wrote a formula (kind of long but useful) that returns to me the difference between dates in Years, Months and Days (No metter if the date is greater than or lower than today’s date).

    Write in A1 the date you want investigate.
    In B1 write the following formula:

    =IF(A1=””,””,IF(A1<=TODAY(),TEXT(TODAY()-A1,"YYYY")-1900&" Years "&MONTH(TODAY()-A1)-1&" Months "&DAY(TODAY()-A1)&" Days",TEXT(A1-TODAY(),"YYYY")-1900&" Year(s) "&MONTH(A1-TODAY())-1&" Month(s) "&DAY(A1-TODAY())&" Day(s)"))

    This formula allows me to know how many Years, Months and Days had passed or remain from today's date.

    I hope this little contribution to time travelers make them all happy. The most important is to share our knowledge in pro of many and I will be happy that someone out there will find a solution for his/her issue.

    Best regards to all and please have a great day.
    (Saludos cordiales a todos y que tengan un gran día).

  8. Amit says:

    Next Month First Monday – =EOMONTH(A1,0)+CHOOSE(WEEKDAY(EOMONTH(A1,0)),1,7,6,5,4,3,2)

    Christmas
    =IF(A1-DATE(YEAR(A1),12,25)<0,DATE(YEAR(A1),12,31)-6,DATE(YEAR(A1),12,31)-6+365)

  9. Bryan says:

    For the Christmas formula I got this: =DATE(IF((DATE(YEAR(A1),12,25)-A1)>182,YEAR(A1)-1,YEAR(A1)), 12, 25)

  10. Amyth says:

    #15. To find the day of week for TODAY, use =WEEKDAY(TODAY()). This will give a number (1 to 7, 1 for Sunday, 7 for Saturday).

    if we use =weekday(today(),2) it shows the exact day starting from 1 for Monday and 7 for Sunday…

    Please correct if i am wrong

  11. Roy says:

    Great article!! Very handy and useful.

    Please write more article like this, putting all similar tips together.

    Thanks Chandoo!

  12. MF says:

    First Monday next month…
    =DATE(YEAR(A1),MONTH(A1)+1,1)+7-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1),2)+1

    For the Christmas one…
    =IF(ABS(DATE(YEAR(A1),12,25)-A1)>ABS(DATE(YEAR(A1)-1,12,25)-A1),DATE(YEAR(A1)-1,12,25),DATE(YEAR(A1),12,25))

    btw, I think DATEDIF is a very helpful but less known Function to calculate the difference of date.

    • Krishna R M says:

      Dear MF,

      your first monday next month formula gives wrong answer if 1st day of next month itself is Monday e.g if the input is any day in Aug 2014, the result by your formula is 08-Sept-2014, however the right answer would be 01-Sept-2013.
      Consider this small alteration/correction to your formula:
      =IF((WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1),2))=1,DATE(YEAR(A1),MONTH(A1)+1,1),(DATE(YEAR(A1),MONTH(A1)+1,1)+7-(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1),2))+1))

      Regards

  13. Krishna R M says:

    Very informative post. Thank you.

    My answers for quiz are:

    Next Christmas:
    =IF(DATE(YEAR(A1),12,25)-A1>(A1-DATE(YEAR(A1)-1,12,25)),DATE(YEAR(A1)-1,12,25),DATE(YEAR(A1),12,25))

    First Monday next month:
    =IF((WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1),2))=1,DATE(YEAR(A1),MONTH(A1)+1,1),(DATE(YEAR(A1),MONTH(A1)+1,1)+7-(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1),2))+1))

  14. Xiq says:

    Here is my Christmas solution :D

    =DATE(YEAR(A1)-(A1-DATE(YEAR(A1),1,1)+7<DATE(YEAR(A1),12,31)-A1-7)*1,12,25)

  15. Hamza asghar says:

    Dear Sir,
    You are great and your efforts are also appreciable. and now i have so many hopes with you and i am sure that you will take a prompt action on my request.
    my request is, i am too busy person i have a lot of work to do on daily basis and an other side i am hungry to learn MS Excel , but due to lake of time i cant give me concentrate to Excel and i am sure that you can make it easy for me. my request is kindly sent me all the formulas along with brief detail on a sequence of sheet. i hope its will be help full for me. i am waiting for your reply. Take care GOD bless you and your whole team.

  16. Hamza asghar says:

    Dear Sir,
    You are great and your efforts are also appreciable. and now i have so many hopes with you and i am sure that you will take a prompt action on my request.
    my request is, i am too busy person i have a lot of work to do on daily basis and an other side i am hungry to learn MS Excel , but due to lake of time i cant give me concentrate to Excel and i am sure that you can make it easy for me. my request is kindly sent me all the formulas along with brief detail on a sequence of sheet. i hope its will be help full for me. i am waiting for your reply. Take care GOD bless you and your whole team.

  17. Venky says:

    For Next christmas

    =IF(OR(DAY(A1)<25,MONTH(A1)<12),DATE(YEAR(A1),12,25),DATE(YEAR(A1)+1,12,25))

  18. Brij Arora says:

    For first monday next month another way around
    surely not elegant but still works

    =IF(WEEKDAY(EOMONTH(A1,0)+1)<=2,B1,(B1+9-WEEKDAY(EOMONTH(A1,0)+1)))

    working on xmas will post once done.

  19. Brij Arora says:

    for nearest xmas

    =IF((DATE(YEAR(A1),12,25)-A1)<183,DATE(YEAR(A1),12,25),DATE(YEAR(A1)-1,12,25))

  20. ramksingh says:

    I need to get Surnames form given data-
    raj kumar singh (34567)
    harish Chandra prajapati (45890)

  21. ramksingh says:

    Please count those Names which has surname “Kumar” in end.
    What would be the formula-

    Raj Kapoor
    Vikash Kumar
    Hari Singh
    Rakesh Kumar

  22. […] following except is taken from Chandoo’s blog on time and date functions. It’s just a bookmark post. All credits to […]

  23. Abdelkader Maaroufi says:

    First Monday Next Month one (Monday is the first day)

    =EOMONTH(A1;0)+(8-WEEKDAY(EOMONTH(A1;0);2))

  24. Abdelkader Maaroufi says:

    sorry if I’am late, but this is my Xmas formula it’s all about the 25/06/AAAA Date…

    =IF(A1>DATE(YEAR(A1);6;25);DATE(YEAR(A1);12;25);DATE(YEAR(A1)-1;12;25))

  25. Siddharth Panvalkar says:

    For First Monday :

    =EOMONTH(A1,0)+CHOOSE(WEEKDAY(EOMONTH(A1,0)),2,1,6,5,4,3,2)

    For the nearest Christmas : I have gone step wise .

    Step 1 : – ascertain whether the same is a leap year or not and compute the cut off date accordingly by adding either 183/182 days. The formula for the same is :
    IF(MOD(YEAR(A1),4)=0,DATE(YEAR(A1),1,1)+183,DATE(YEAR(A1),1,1)+182). Once we compute the cut-off date which is July 2. Then we compare the date in cell A1 with July 2 and compute the nearest Christmas day accordingly.
    =IF(A30>=B28,DATE(YEAR(A30),12,25),DATE(YEAR(A30)-1,12,25))

  26. stansult says:

    > dates prior to 1st of January 1900

    Funny, but its in fact “0th of January 1900”.
    You can enter “1/1/1900” into A1, and have “=A1-1” successfully calculated in another cell.
    But after that (like “=A1-2” and so on) would be already “########”

  27. Rahul says:

    Great and very useful to know….Would request you to email me a list of useful formulas and functions.

    Thanks in advance!

    Rahul

  28. hmjjbe says:

    On #35 does anyone know how to go to the last day of the next month instead of the 18th? The formula does not seem to work after 29 or 30 days. Thanks!

    35. To go to 18th of next month, use =DATE(YEAR(A1), MONTH(A1)+1, 18)

  29. Hui... says:

    In regards to No 22.
    First day of this month =DATE(YEAR(A1), MONTH(A1),1)

    I prefer =Eomonth(A1,-1)+1

  30. Hui... says:

    In regards No 14.
    To see the last 2 digits of the year, you can use =RIGHT(YEAR(TODAY()), 2)

    or simply use a Custom Number Format of YY
    or via a formula =Text(A1,”YY”)

  31. Vad says:

    My version of close Christmas date, though not tested regressive -

    DATE(ROUND(MONTH(A1)/12,0)+YEAR(A1),12,25)

  32. Tam says:

    I have a column of date/time in GMT (UTC)
    I want to display in Pacific time (-7 hrs daylight savings) Is there an easy formula that will change both the time and the date if if the fallback time goes into the previous date?

  33. Tyler says:

    I’m having a hard time converting the date in my excel workbook.

    I was given the dates for an entire year as 10111, representing January 1, 2011, and so on.

    How can I get excel to give me the date in 1/01/11 format in a different cell?

  34. meliza says:

    I am having a hard time trying to figure out something. How do you compute 12/3/13 then I need the date of the following thursday.

  35. mirza says:

    what i do? if i need the no of day when i put the date
    exmpel, 13/3/2014 = 74

    • Hui... says:

      @Mirza
      If in your example the 13/3/2014 should have returned 72 instead of 74
      The answer is :
      =DATE(2014,3,13)-DATE(2014,1,1)+1
      or
      =DAYS360(“1/1/2014″,”13/3/2014″)

  36. Ray Solanki says:

    I am using microsoft 2010 excel and I want to use a formula to work 6 weeks from 26/06/14 – please can you kindly help.

    Thank you

    Ray

Leave a Reply