Comments on: Excel date functions with formula examples

This is the final part of our Excel Date Tutorial that offers an overview of all Excel date functions, explains their basic uses and provides lots of formula examples. Continue reading →

Comments page 6. Total comments: 595

  1. HI,

    I would like to know that i have date in a column(Ex: A2) which has to find if the same date fall between any given period.

    Ex: 10-Feb-2021 located in column A2

    Period: 1-Feb-2021 to 28-Feb-201 (located in another column)

    Same kind of details need to find out multiple employee leave details

    if A2 date fall between that particular period then i need to keep the remarks or need to highlight.

    Thanks in advance for your assistance

  2. I am trying to figure out how to insert a date if the cell is not blank. As in when A2 is not blank, then B2 fills in today's date. I have tried various formulas and keep getting an error.

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =IF(A2<>"", IF(B2="",NOW(), B2), "")

      To prevent your date from automatically changing, you can use several methods:
      1. Use the recommendations from this article in our blog.
      2. Replace the date and time returned by the TODAY function with their values. Copy the date (CTRL + C), then paste only the values using Paste Special or Shortcut CTRL + ALT + V.
      I hope my advice will help you solve your task.

  3. Is there a formula I can apply to two columns that automatically adjusts the dates by one should the start day be changed. For example, if I have a start date of 03/01/2021 and I change it to 03/02/2021, it automatically shifts the dates in the Start Date and End Date column below by however many days its been changed or by one day off what they currently are? I currently use excel to track tasks for construction projects and have manually changed all dates. This is time consuming but necessary to track progress. I appreciate your time.

    1. Hello!
      An Excel formula can only change the value of the cell in which it is written. In the columns Start Date and End Date, you need to write formulas that will automatically change dates. The information you have provided is not sufficient to provide more accurate advice.
      Describe in detail what problem you have, and I will try to help you.

  4. I wanted to prepare aging report for receivables with overdue days and what would be the formula if payment received

    1. Hello!
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? What result do you want to get? Give an example of the source data and the expected result.

  5. I have a monthly payments due date what is the formula? Also I have couple of yearly (annually) payment due date so what is the formula for yearly date?

  6. Hi Team. I have calendar month, range from 1 to 30 day in cell A1 to A30 to and i have in cell A10=14, A11= 7. I have another work sheet call STAT and i wanted to display the SUM of A10 and A11 to dynamically for each day. The issue for me is looping each day dynamically. Hope you can help

    1. Hi,
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you.

  7. Is there a way to have the current date that will not update? I'm trying to track compliance dates - so for example when column D=0 column E will show the date that D first equaled 0.

      1. Yes! - Thank you!!

  8. I’m a senior nurse trying to figure out what formula I can use to get the duration (in hours) between 2 dates with times.
    The way the cells are configured is
    08/10/2018 06:00
    06/11/2018 19:00
    Please help

    1. Hello!
      Use a subtraction formula

      =B2-A2

      In a cell with a formula, apply a custom time format

      "37:30:55"

      I hope this will help, otherwise please do not hesitate to contact me anytime.

      1. Amazing thank you

  9. I need to include start date as well end date so what to do. I just did A2-B2+1. any other formula

    1. Hi,
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? What result do you want to get? Give an example of the source data and the expected result.

  10. Hello,

    I have a document that has a date in text form listed like Nov/19. When I use the date value formula, the formula converts it to Nov/20. Is there a formula I can use that will convert the text to the correct date?

    1. Hello!
      Unfortunately, I was unable to repeat your mistake. Please state exactly how your date is written. What formula are you using? What is the default date format?

  11. Hi Team,

    I was wondering if you could help with task have in hand. So i have a team monthly rota. The dates usually start for example 16th Nov - 13th Dec and another 14th Dec - 10 Dec and so on. In the rota column the dates are format horizontally as below:
    16 17 18 19 20 21 22 23
    Mon Tue Wed Thur Fri Sat Sun Mon. and so on.

    Task: Am trying to populate data from the rota and another worksheet dynamically, have actually complete the task. But, need to select each month of the from a drop down menu and the data for that month will be populate along with the already VLOOPUP formulars.

    Problem: How do i convert does weekly dates to 1 months and create a drop down menu as per the rota 16th Nov - 13th Dec and 14th Dec - 10 Dec list.

    So i understand in formula is: =IF(above weekly date = 16th Nov - 13th Dec from dropdowm list, VLOOPUP(A1, ARRAY, COLUMN, FALSE), "","")

    I can as well have a separate sheet where i can store the weekly date in month and create a validation list from there.

    I hope this make sence, if not i can send you my screenshot data and you can have a look.

    Looking forward to your swift response.

    Thanks

  12. Hi Team,

    I have a date in one column A and the severity Level in Column B and i am trying to get the future due date in the column C. If the severity Level is Critical in column C then the future date should be after 30 days from the date mentioned in Column A.

    Can anyone help me with this formula.

    1. Hello!
      Sorry, I do not fully understand the task. What does "severity Level"? Please describe your problem in more detail. Include an example of the source data and the result you want to get. It’ll help me understand your request better and find a solution for you.

      1. Hi Team,

        I am Cyber security Analyst and i am planning to automate teh report which in MS excel.
        In one column there will be CVE ID and in next column it's severity level (critical, High, Medium and low) and CVE released date, based on these 3 things i have to update the future date when the Bug or patch has to fixed in our environment.
        If the severity Level is Critical then the future date should be after 30 days from the date of CVE release.

        1. Hello!
          If I understand your task correctly, the following formula should work for you:

          =IF(B2="critical",C2+30,"")

          I hope it’ll be helpful.

  13. Hi Expert,

    How do I make the cell auto change for Due date (a fixed date) when the date is change (Actual Start Date - Plan Start Date(a fix date)). Can someone help please ?
    Example:
    Plan Start Date: 01/11/2020 (fixed)
    Actual Start Date: 05/11/2020
    Difference: 5 days
    Due date : 30/10/2020 (fixed) + 5 days - this cell will auto change to 05/11/2020

    So basically whenever have changes to Actual Start Date, Due date cell will change automatically based on the difference days count.
    What's the formula to use in this situation ?i Thanks !

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =C1+(B1-A1)

      A1 -start date
      B1 - actual start date
      C1 - due date

      1. Hi Alexander,

        Thanks for the reply, appreciate it. But that is not that what i want.

        Plan Date Actual Start Date
        01-Nov (A1) 05-Nov (B1)

        Due date
        30-Oct (B4)
        03-Nov (B5)

        My task is to make B4 and B5 to change automatically based on the difference between B1-A1. So my question is, what formula to put in cell B4 and B5 (already has a date in the cell) to make it both auto change based on the day difference. Hope it more clearer for you.

        Appreciate much your helps !

        1. Hello!
          On our forum, we have already written many times that if there is some value in a cell, then the formula cannot be written into it. Your task can be solved using the VBA macro. It is impossible to solve it using an Excel formula.

  14. Dear Sir,
    Please guide me how to change next date into last date in excel

    thanks & regard

  15. I'm trying to populate todays date when a cell is not blank. Here is what I have for a formula:
    =IF(ISBLANK([@[Shipping Release]]),"",TODAY())
    I dont want the entire column to change to todays date but instead populate the day that information was input and not automatically update. Any ideas?

  16. Hi,
    Am still using excel 2007. So Daily making invoices by date by date. Suddenly when I open the old invoice for checks it’s showing as TODAYs date(current) .. so I need to solve that when opening old document. is there anyone can help me plz

  17. Hi,
    Is there any formula to return previous day if the time is between 00:00 ~ 05:00 hrs

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =IF(AND(HOUR(B1)>0,HOUR(B1)<5),B1-1,B1)

  18. I can't seem to figure out which combination of formulas i need to create the following result. One column has the date of last purchase of an item and the cell next to it would have a formula that would take that date and add a quantity of days to get the date in the future, and be able to change when the date of last purchase changes. So as a specific example last purchase date is 6/19/2020 the cell next to it then would populate the date 90 days in the future which would be 9/17/2020. Then on 9/17/2020, the cell would update with the date 90 days from that once i enter it in the 'last purchase' cell. Thanks for the help!

  19. Hello Alex,
    What if instead of invoice date, it should start from the end of transaction week? Every Friday is the starting date but the invoice date is any date like April 1 Wednesday and 13, 2020 Monday. Thanks

    1. Hello Myra!
      It is a pity that you did not immediately indicate all the conditions. It would take significantly less time.
      Please try the following formula

      =A3+(5 > WEEKDAY(A3,2))*(5-WEEKDAY(A3,2))+(5 < WEEKDAY(A3,2))*(12-WEEKDAY(A3,2))+57

      A3 is the invoice date.

      1. Hello Alex,
        Sorry for the late reply. Wow, that's a long formula. Will try to understand this. Thank you for helping me. Stay safe always. :)

  20. Hello,
    How do I calculate the collection date when 57 days credit term starts from the end of each transaction week?
    Invoice date is April 1, 2020

    1. Hello Myra!
      I’m sorry but your task is not entirely clear to me.
      For me to be able to help you better, please describe your task in more detail. Please let me know in more detail what you were trying to find, what formula you used and what problem or error occurred. It’ll help me understand it better and find a solution for you. Thank you.

      1. Hello Alex,
        Sorry for the confusion. I'm not familiar with formulas but I can provide you the details. I'm trying to compute the due date of my invoice. Credit term is 57 days. It starts from the end of transaction week. Invoice date is April 1, 2020. Hope you could help me. Thanks.

        1. Hello Myra!
          If I understand your task correctly, the following formula should work for you:

          =B13+(7-WEEKDAY(B1,2))+58

          In this formula, the countdown starts on Monday of next week.
          I hope this will help, otherwise please do not hesitate to contact me anytime.

          1. Hello Alex,
            Thank you for providing me the formula. May I know how did you come up with this? B1 is for the cell for invoice date? Why using B13, (B1, 2), 58? Sorry not familiar with formulas.
            Thank you and stay safe always.

            1. Hello Alex,
              What date should I get if invoice is for April 1, 2020 and April 14?
              Do I need to change the formula for April 14?
              Thanks.

              1. Hello Myra!
                Write this date in B1, and write the formula in any other cell. The formula determines the date of next Monday and adds 57 days

            2. Hello Myra!
              I'm sorry, accidentally wrote an extra digit. Formula -
              = B1 + (7-WEEKDAY (B1,2)) + 58
              B1 is the invoice date.

  21. What is the formula to put two strings together. I need =DATEDIF(A1,A2,"M") but if the A2 is blank calculate by "today" =DATEDIF(A1,"TODAY(),"M")

    1. Hello Bonnie!
      The formula below will do the trick for you:

      =IF(A2="",DATEDIF(A1,TODAY(),"m"), DATEDIF(A1,A2,"m"))

      I hope it’ll be helpful.

      1. hi sir

        if 07-Aug-19 : 26 -Feb-2020 =DATEDIF(07-Aug-19,26 -Feb-2020,"d") =569days and the next month

        7-Sep-20219: 26-Feb-2020 =DATEDIF(07-Sep-19,26-Feb-2020,"d") =538days

        how to formulate the total days in 19months" 07-Aug-19, to 26 -Feb-2020 =5,592

        1. Hi,
          What do you want to calculate exactly? Your question is not entirely clear, please specify.
          My guess is the date should be 26 -Feb-2021.
          Explain which 19 months you are talking about: " total days in 19months” 07-Aug-19, to 26 -Feb-2020 =5,592"? It's 203 days.

  22. Hi,
    How do I calculate the days completed based on the ()Todays (current date) from a start date and an end date, please?
    Example:
    Start Date: 20/04/2020
    End Date: 10/05/2020
    Today's Date: 26/04/2020
    Numbers of days completed:?
    What's the formula to calculate the number of days completed, taking into account the end date?

    1. Hello Emmanuel!
      If I understand your task correctly, please try the following formula:

      =DATEDIF(A1,TODAY(),"d")

      where A1 - Start date.
      You can learn more about DATEDIF in this article on our blog.
      Hope you’ll find this information helpful.

  23. WHAT IS THE FORMULA TO HAVE A DATE CHANGE COLOR (YELLOW) 30 DAYS PRIOR TO THE DATE SHOWN AND CHANGE COLOR (RED) AFTER THE DATE SHOWN

  24. Could I use =DATE ( for copying another date?

  25. I'm trying to concatenate 2 dates (arrival and departure) so that the result looks like this: Feb 2 - Feb 5 or, if there's a month boundary: OCT 28 - NOV 20

    I can't get a formula to work using DATE or TEXT, etc. For example:
    =IF(TEXT(D2,"mmm")),TEXT(E2,"mmm"))),CONCAT(CONCAT(TEXT(D2,"mmm","/",TEXT(D2,"dd"))....

    There are no helpful error messages.
    Any ideas?

    Thanks.

  26. Is there a formula or an option that will restrict Now() and Today() function to update automatically? I want them to stay fixed from the day I select "yes" on the cell.

    This are my current function commands:
    =IF(I4="","",IF(I4="yes",TODAY(),"Check SP"))
    =IF(I5="","",IF(I5="yes",NOW(),"Check SP"))

  27. I want list of formulas to change the date range 01/01/2020 - 01/31/2020 with formula.

    If in between I 'm changing the date then it should continue from the date i changed.

    Thanks,
    Mustakeem Qureshi

  28. Hi all,
    I need formula that will count only number of days that have passed - 1 day, for each month.
    =DATEDIF(A2, TODAY(), "d") this formula counts number of days that have passed since specific date, I need end that also.

    Which means the final number for January should be 31, for February 28(29), for March 31.

    Thank you

  29. Hi,

    How do I add a leap year into an excel formula. I have one set for the Julian calendar which works off a number per day of the year for each of the 365 days. However, I cannot get it to figure out leap years. The formula I am using at the minute is: =IF(C2="","",DATE(YEAR(TODAY()),1,C5)). C2 is where we put the code and C5 is the date.

    Thanks

    1. Hi Matthew,
      The same formula you sent will work in leap year too. It will simply consider February 29th as the 60th day of the year.

      If however, you need to check if the year is leap or not, here is the formula for you:
      =IF(MOD(YEAR(A1), 4), "normal year", "leap year")

      Where A1 is the cell with a date.

  30. I am trying to find the baseline percentage of training hours that an employe should be at on the current day. So if an employee has 3000 minutes worth of training to do I would like to have a cell that tells me the percentage that the should have completed on that day.

  31. I have a field like "Thursday, 11/7/2019" how to extract only the date without the day of the week.

    Thank you,

  32. i try to find the remaining day, i try all formula but showing only "VALUE" command only
    what i want to do..?

  33. I need to calculate prorated days for real estate closings automatically for the tax prorations. I have everything figured out except I have to manually enter the prorated date. For example, house closes june 1st, it will always calculate days until june 30th. I simply have a formula subtracting june 30th from june 1st to give me number of days, however, i have to constantly monitor the 6/30 date to make sure the year is the following june 30th, I'd like to automate this. how do i enter a formula that says I want this cell to say 6/30/(after todays date)? so, if today is 8/23/19 I want the prorated date to read 6/30/2020. If it were say, 4/30/19, I want the prorated date to read 6/30/19, so always the june 30th after whatever date.

  34. Hi,

    If I use the formula for today's date, will the date update every day?
    I'm looking for a formula to log the current date when a certain value is reached, but if the TODAY formula updates to current day I won't be able to log the date the value is reached.
    Can someone please clarify how this works? And if it does only give the current date, can you please let me know if there is a formula to log the current date and not update daily?

    Thanks,
    Tyler

    1. =IF(B9>0, TODAY(), "" )

      8 | A | B |
      9 | 12/14/2019 | Reachable value |
      10 | | If Reachable is Null then A-10 show is empty |

    2. Hi Tyler,

      Yes, the TODAY formula updates automatically to always show the current date.

      If you are looking to insert today's date as an unchangeable time stamp, this can be done with the Ctrl + ; shortcut or a more complex formula that uses a circular reference. You can find full details in How to insert today date & current time as unchangeable time stamp. However, using circular references in Excel is always a risk, so please be sure to weigh all pros and cons carefully before using that formula in your worksheets.

    3. not sure what you are asking, current date is not current if it doesn't update

  35. start date and end date is greater than 6 months then count full year.
    for example
    01-01-2000 to 02-04-2019 the answer is 28 year 6 months and 1 day
    but i get the only 29 year only
    01-01-2000 to 01-04-2019 the answer is 28 year 5 months and 30 days
    but i get the only 28 year only
    any formula in excel

    1. try it
      =DATEDIF(B1,B2,"y")&" Years " & DATEDIF(B1,B2,"ym")&" months " & DATEDIF(B1,B2,"md")&" days "

      1. Hi .. i have problem , how to make month and year only to combine, and otomatis.
        example :

        the label show only "2212" how to make this formula
        thank you

    2. try to this type of formula you will get

  36. I need to make daily sign-in sheets for company visitors. Is there any way to make one sign-in sheet and have the working days populate for the rest of the month?

    1. I can help you out for your query.But tell me one thing that you said "1 sign-in-sheet and have the working days populate for the rest of the month". Does this mean you want to calculate the present days for the visitors or the remaining days of that particular month?

      1. Not OP, but it would be great to calculate the present days for the visitors up to a certain date. For example, a sign-in-sheet that begins at a certain date, counts up to, and then ends after a period of time like 3 months.

  37. How to get the number of remaining days for a specific date, eg- if A1=29 I need 2 as a return in B1 if today's date is 27, same way if A1=3 I need 3 as a return in B1 if today is the last day of month.
    So please suggest any formula for this, if there is any.

  38. what is formula for date.
    turn color or highlight when future date become current date.
    for example if date enter in cell 15-02-2025 and when computer date become 15-02-2025 it highlighted or color turn

  39. I want to be able to click and drag a formula to add 7 days like as follows

    ABC 01/14/2019 MIC 01/14/2019 XYZ 01/14/2019 ABC 01/21/2019 MIC 01/21/2019 XYZ 01/21/2019 .....

    None of the formulas above seem to allow something like this, my sheet needs 4 columns for each date, and then the next 4 columns to be 7 days after the previous 4 columns, but each has the 3 digit prefix for the date.

  40. Hi svetlana
    i have a excel problem can you help me
    below mention table include some employee numbers and their "IN & OUT" in all one row ..how can i get this "IN & OUT" to two rows with according to employee numbers
    Emp no and In And Out
    4 01/12/2018 7:49
    6 01/12/2018 17:02
    1 01/12/2018 17:03
    4 01/12/2018 17:03
    9 01/12/2018 17:03

    52 03/12/2018 8:03
    26 03/12/2018 8:03
    6 03/12/2018 8:03
    9 03/12/2018 8:06
    2 03/12/2018 8:11
    1 03/12/2018 8:32
    40 03/12/2018 9:13
    4 03/12/2018 9:31
    1 03/12/2018 17:56
    52 03/12/2018 17:59
    40 03/12/2018 17:59
    6 03/12/2018 17:59
    26 03/12/2018 17:59
    9 03/12/2018 17:59
    4 03/12/2018 17:59
    2 03/12/2018 18:00
    52 04/12/2018 8:00
    26 04/12/2018 8:00
    9 04/12/2018 8:01
    1 04/12/2018 8:03
    2 04/12/2018 8:05
    6 04/12/2018 8:06
    40 04/12/2018 8:56

  41. Hi Svetlana,

    I am working on Task Manager for 2019 and facing issue while retrieving the weekend date for current week (whichever it may be) excluding holidays and weekends.

    I am using below formula:
    =WORKDAY.INTL($L$1,NETWORKDAYS.INTL($L$1,$R$1,1,$Y$4:$Y$14),1,$Y$4:$Y$14)

    $L$1- current date
    $R$1- weekend date
    $Y$4:$Y$14 - holiday list

    It is working for normal week but fails whenever there is hoilday.

    Thanks in advance.

  42. Hi,
    I want to validate a cell with current date. if it is less than current date then the color of the cell should red or if it is equal to current date then the color should be green.

    Regards,

    Bibhu

  43. Hello, please let me know if a return of "year.month.day" is possible. So the column A would be:
    2018.01.01
    2018.02.24
    2018.03.13
    Etc,

    Thanks.

    1. Hello!

      If column A already contains dates, you can simply set this custom format for them: yyyy.mm.dd

      For this, select the dates, press Ctrl+1, on the Number tab select Custom in the Category list, and type the above code in the Type box. For more information, please see How to create a custom date format in Excel.

  44. Ineed the persian date(1397)in excel i tried alot but exel only has two date ie gregorean and hijri qamari date.the microsoft must instaled the persian date too.so now how can i solve my problem.

  45. How to mention a date in salary sleep, if employee is absent on specific date?

  46. Hello,
    I kind of need some help. I have make a table containing dates which are deadlines. A side there is also a final deadline for that entire table. What I want to do, if any of the date changes the final deadline date should automatically change for the number of days which changed on that one date. Is it possible to accomplish that in excel and if it is, how can I do it? :)

    Thank you in advance. :)

  47. I have a column that calculates a projected date based on another column that explains the number of days that either needs to be added or subtracted and then posted as a projected and specific date so we can post to a calendar as an alert. However, what I REALLY need is a formula that shows the previous FRIDAY if or when it falls on a weekend or holiday.

    HELP and THANKS!

    -steve

  48. Hi,

    Can i get an exact date from this only "Sept-2018"

    I am creating a table where I just need to replace the specified cells details then change the date on one column.

    Thanks ahead!

    1. I think not possible

  49. Hello,

    I am trying to modify a spreadsheet to highlight the dates I have to make a status call. I work at a financial firm, every time we process transfer paperwork, we have to call the contra firm 8 business days after mailing it, then every 5 business days after that. I would like excel to not only automatically generate these dates in at least three different columns, but I want it to automatically highlight the dates once they've approached. Are you able to provide a formula for this?

  50. DATE 01.01.18 AFTER 30 DAYS DATE WE NEED AND DATE IS THIS FORMAT ONLY ( EX:31.01.18 ). PLEASE SEND THE EXCEL FORMULA

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)