arrow71 Comments
  1. Rob T
    Oct 17 - 10:03 am

    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
      Oct 17 - 10:10 am

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

      • Rob T
        Oct 17 - 11:07 am

        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
          Oct 17 - 11:23 am

          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 πŸ™‚

    • John Beacham
      Apr 22 - 1:29 am

      Here are my formulae
      A formula to take date in A1 to next month’s first Monday.
      =EOMONTH(A1,0)+IF(WEEKDAY(EOMONTH(A1,0)+1)=2,1,IF(WEEKDAY(EOMONTH(A1,0)+1)>2,(8-WEEKDAY(EOMONTH(A1,0)+1))+2,2))
      Given a date in A1, find out the closest Christmas date to it.
      =IF(MONTH(A1)<=6,(EOMONTH(A1,-MONTH(A1))-6),(EOMONTH(A1,11-MONTH(A1))+25))

  2. Rob T
    Oct 17 - 11:40 am

    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
    Oct 17 - 11:49 am

    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?

    • Abhilash VK
      Oct 17 - 3:41 pm

      @Rich, Camtesia Studio will record the video and the same can be save as .GIF extension

  4. Rob T
    Oct 17 - 1:26 pm

    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
    Oct 17 - 3:23 pm

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

  6. J Gutz
    Oct 17 - 5:03 pm

    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
    Oct 17 - 5:18 pm

    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).

    • Bob G
      Oct 19 - 4:56 pm

      Anyone else getting an error trying to use this formula?

      • Marc S.
        Oct 23 - 4:52 pm

        Bob, Go in and replace the “”.

  8. Amit
    Oct 17 - 5:24 pm

    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
    Oct 17 - 8:10 pm

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

    • Xiq
      Oct 18 - 9:26 am

      Here is a little modified version of your formula:

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

  10. Amyth
    Oct 17 - 8:57 pm

    #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
    Oct 18 - 1:07 am

    Great article!! Very handy and useful.

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

    Thanks Chandoo!

  12. MF
    Oct 18 - 1:31 am

    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
      Oct 18 - 5:08 am

      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

      • MF
        Oct 21 - 6:53 am

        Hi Krishna,
        You are right. I overlooked that. Thanks for pointing it out! πŸ™‚

  13. Krishna R M
    Oct 18 - 4:56 am

    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
    Oct 18 - 9:12 am

    Here is my Christmas solution πŸ˜€

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

  15. Hamza asghar
    Oct 19 - 5:59 am

    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
    Oct 19 - 5:59 am

    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
    Oct 19 - 6:43 am

    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
    Oct 19 - 11:03 am

    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
    Oct 19 - 11:09 am

    for nearest xmas

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

  20. ramksingh
    Oct 19 - 5:10 pm

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

  21. ramksingh
    Oct 19 - 5:17 pm

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

    Raj Kapoor
    Vikash Kumar
    Hari Singh
    Rakesh Kumar

    • gsvirdi
      Apr 22 - 7:08 am

      Didn’t tried Data > Split text to column

      ???

  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
    Oct 21 - 10:59 pm

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

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

  24. Abdelkader Maaroufi
    Oct 23 - 8:33 pm

    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
    Oct 24 - 9:17 am

    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
    Oct 28 - 6:07 pm

    > 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
    Oct 30 - 1:07 am

    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
    Oct 30 - 8:59 pm

    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)

    • Hui...
      Oct 31 - 12:00 am

      @Hmjjbe
      Last day of this month =Eomonth(A1,0)
      Last day of next month =Eomonth(A1,1)
      Last day of last month =Eomonth(A1,-1)

      • Kris
        Apr 21 - 4:08 pm

        I typed
        Last day of this month =Eomonth(A1,0)
        Last day of next month =Eomonth(A1,1)
        Last day of last month =Eomonth(A1,-1)

        Result
        A1 B1 C1 D1
        4/21/2016 42490 42521 42460

        Is this right?

  29. Hui...
    Oct 31 - 12:05 am

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

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

  30. Hui...
    Oct 31 - 12:12 am

    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
    Oct 31 - 7:45 am

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

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

  32. Tam
    Nov 01 - 2:36 pm

    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
    Dec 30 - 9:57 pm

    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
    Feb 13 - 1:17 am

    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.

    • Hui...
      Feb 13 - 2:53 am

      @Meliza

      Try: =A1+8-WEEKDAY(A1+3)

  35. mirza
    Mar 13 - 12:40 pm

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

    • Hui...
      Mar 13 - 2:16 pm

      @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
    May 02 - 2:00 pm

    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

  37. Eva Pettifor
    Aug 13 - 2:21 am

    Thank you so much for your great tips. I have just used the instructions to calculate a past date for a column of cells that had to display the 6 week prior date. Much appreciated

  38. kumar
    Oct 31 - 6:20 am

    hi,

    i have two excel sheets
    one sheet is total mtd data and one sheet is areas data
    ineed today out of 100 areas 10 areas completed survey
    after that next day ineed the that 10 areas any peopole called in survey after 7 days
    please provide the any formula

  39. […] Excel Date & Time tips – How to calculate tomorrows date … – Learn how to use various Excel functions & techniques to work with dates & time values. Understand how to write formulas for tomorrow’s date, last week’s date,… […]

  40. […] Excel Date & Time tips – How to calculate tomorrows date … – Learn how to use various Excel functions & techniques to work with dates & time values. Understand how to write formulas for tomorrow’s date, last week’s date,… […]

  41. Sanjeev Kumar
    Jan 17 - 1:20 pm

    Hi team we required help on calculating the time in a working hours need to be calculated in 24 hours 8:00AM to 8:00PM timing need to calculate in a 7 days or 1, 1/2, days etc we need to calculate only working days, please suggest.

  42. Amit
    Jan 18 - 9:25 am

    Hi Sanjeev,

    Please share more details, the answer would depend on what kind of data, you have in your sheet

  43. Tarun
    Jan 21 - 1:41 pm

    Could you please suggest me a formula by which I can find the last Friday of previous month.
    Below formulas are to find last Friday of current month:
    =DATE(YEAR(H10),MONTH(H10)+1,1)-WEEKDAY(DATE(YEAR(H10),MONTH(H10)+1,1)-6)
    =EOMONTH(H10,0)+1-WEEKDAY(EOMONTH(H10,0)+1-6)
    =EOMONTH(H10,0)-MOD(WEEKDAY(EOMONTH(H10,0))+1,7)

    • Chris
      Jan 22 - 3:49 pm

      You can try this to calculate the date of the last friday in the previous month, note that EOMONTH can have negative or positive numbers:
      =EOMONTH(H10,-1)+1-WEEKDAY(EOMONTH(H10,-1)+1-6)

  44. Amit
    Jan 22 - 2:40 pm

    Please find the answer below –

    =EOMONTH(TODAY(),I2)+IF(WEEKDAY(EOMONTH(TODAY(),-1))>5,6-WEEKDAY(EOMONTH(TODAY(),-1)),-1-WEEKDAY(EOMONTH(TODAY(),-1)))

  45. Nerd345
    Mar 05 - 9:34 am

    Hi. Please help me figure this one out. If A1=year and A2=week number and A3=weekday, what formula do I type into B1 to get the date? I haven’t found anything allowing finding a date using the week number as part of the ‘known’ data. Thanks.

  46. Toni
    Mar 13 - 5:03 pm

    I have a spreadsheet that has a date column and a time column. The time has to advance every 10 seconds and the date must change at the 24 hour mark. For example – I start on 03/13/2015 the date advances a cell for every 10 seconds (Column B has the time starting at 00:00). So 10 seconds after the 24 hour mark the date should advance to 03/14/2014 and the time should start over at 00:00:00

    PLEASE help me since I cannot seem to find a solution that is consistent!

  47. Coopersmommy
    Nov 23 - 7:16 pm

    Can this be done….if i enter a time in column A, column B then adds 45 mins =a3 +TIMEVALUE(“00:45:00”) can an IF statement be created to notify users that the time is current?
    So if they enter 9:00 in A3 at 9:45 I need an error message or a popup, like ‘ARE YOU SURE?’

  48. Jason
    Apr 22 - 5:45 am

    Hi, Do you have any tips for calculating “overtime” on an employee time sheets?

    Calculating the total number of hours worked is easy but I struggle to calculate which hours should be paid at “overtime rates” without really really over complicating it.

    For example:
    If an employee gets paid $1 hour an hour, and any hours worked between 18:00 to 06:00 are paid at double, what would get get if he worked from 15:00 to 03:00?

    Finish – Start = Total Hours Worked
    15:00 – 03:00 = 12 hours

    A = Hours worked between 06:00 and 18:00
    B = Hours worked between 18:00 and 06:00

    (A x $1) + (B x $2) = $X
    (3 x $1) + (9 x $2) = $21

    Any tips you have for finding A & B would be awesome?

  49. Suman Dey
    Apr 25 - 1:46 am

    Excel Date Ti e Tips

  50. JC
    Apr 25 - 7:56 am

    #38. To know how many days are between 2 dates (in A1 & A2), use =A1-A2

    I prefer =A2-A1 when A1=start date and A2=end date (A1 should be less than A2, like #39.)

    In fact formula =A2-A1 calculates the difference between two dates.
    To calculate duration use another important formula=A2-A1+1.

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

    NETWORKDAYS() calculates duration.

    EXAMPLE:

    If A1 is 4/25/16(monday) and A2 is 4/29/16(friday)
    then:

    =A2-A1 is 4
    =A2-A1+1 is 5
    =NETWORKDAYS(A1, A2) is 5

  51. Earnest Danker
    Jun 15 - 9:49 am

    Hi,
    I’m using formula “=B-A” to get the value of C.
    The formula to obtain the total in column C is : =SUM(D1:D5)
    However, the total is not SUM correctly & notice column C3 is the problem. When I remove it, the Total is correct. Any clues what is in column C3 that is causing this problem ?

    I’m using MS Office 2013. appreciate any response. thanks…ed

    A B C
    15:04 15:10 0:06
    15:10 15:13 0:03
    15:13 15:45 0:32
    15:47 15:48 0:01
    15:56 15:56 0:00

Leave a Reply

Mobile Theme