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 4. Total comments: 595

  1. When I enter a line in Excel, the Today() formula will give, todays date, which is ok, but if I open the Excel sheet tomorrow, it will gives tomorrow's date for every line. What can I add to the formula, to keep the previous dates, at the date of entry and if I enter a new line, it will give automatically today's date? Thanks for all your help in advance,

    1. Not sure you understand me well, but I also tried the following formula: =IF(a2"", IF(b2="", TODAY(), b2), ""), but Excel is not accepting this formula. The plan is as follows: if I enter something at cell A2, it should give todays date and it shouldn't update the date it should remain the same), if I open the excel sheet tomorrow again, it should give tomorrows date for that new line. Hopefully you have an idea to resolve this. Thanks for your help in advance,

  2. Dear Sir,
    When I combine data from 3 columns 125 25.02.2022 P.S. Sadar into one column output result comes just like that125 dated 222222 p.s Sadar
    I want result 125dated 25.02.2022 P.S. Sadar

  3. Dear Sir,
    1. I have the date format in Excel 02/25/2022 I want to get it into 25.02.2022

  4. What formula to use if I want the have the date of the beginning and end of the year to appear automatic and permanent formula to use without changing it yearly

    1. Hi!
      With the DATE function, you can display the start and end dates of the year. What formula you're talking about, I can't guess.
      DATE(YEAR(TODAY()),1,1)
      DATE(YEAR(TODAY()),12,31)

  5. How to make the formula for Year and month automatic , than showing the format is YYMM only.

    thank you for the advise,....

    2. example label need to put in 2212 only,
      but i used the formula =year(today())&month(today()) result is 202212 ,
      202212 need to convert to 2212 only how the formula

  6. In my Gantt chart project start date is 1-Jan,22 8 Am .

    Every day my project runs 8 hours (8am- 4pm)

    How can I calculate the end time with day & time?

    Like 1 day 3 hours need to end project.

    in this way output show 3 jan, 22 11am

    we need to follow the weekend also (like 2 Jan 22 weekend).

    in this way output show 3 jan, 22 11am

    at this moment use the below formula but for decimal day end day cant output the correct answer.

    =WORKDAY.INTL(U21-1,S21,"0000000",$F$1005:$F$1166)

      1. @alexander

        workday.int work properly but i want to calculate day with our for every project as per my estimated day & hour .

        In my Gantt chart project start date is 1-Jan,22 8 Am .

        Every day my project runs 8 hours (8am- 4pm)

        How can I calculate the end time with day & time?

        Like 1 day 3 hours need to end project.

        we need to follow the weekend also (like 2 Jan 22 weekend).

        in this way output show 3 jan, 22 11am

        another demo in below

        Daily Target Estimate DAYS Start Date End Date
        3400 1.96 11-Nov-22 12-Nov-22
        3400 1.90 13-Nov-22 14-Nov-22
        3400 0.71 15-Nov-22 15-Nov-22

        need to calculate time / hour with day

        1. Hi!
          If I understand the problem correctly, use the WORKDAY function to add working days, including holidays. Then calculate time and add hours.

          =WORKDAY(A1,1,C1) + (A1-INT(A1))+3/24

          Hope this is what you need.

  7. I would like to make a Task-completion list with "Date of Completion" in it.

    For that, if 3 of my sub-activities are completed in each task, the Status column will automatically show "Task completed"
    Then i would like to have the date on which it became " Task completed" in the next column. Ho to do that...?

    I tried but How do i get that date to remain like that forever... I used =IF(D1="Task Completed",TODAY(),"") but this gave me changing dates based on the day i opened the excel file... I need the date to be fixed once the cell becomes "Task Completed"...

    Pls help...

      1. Thank you so much... It really helped... :)

  8. Hello there, I just read through the page but didn't find a specific formula for what I am trying - I inherited a spreadsheet that has a column A of "Days old" (working days an item has been in our network) and column B is then the "Due date" of the item. The formula somebody added years ago is =SUM(TODAY()-B2)-1 if it is items after a weekend, and +1 on the end if items during the week that you add. I hope this makes sense(?) - I wanted a macro that can just automatically use todays date and the due date to know how many days the item has been in the network? (and even allowing for bank holidays if possible). I tried NETWORKDAYS but don't seem to be doing it properly? Anyway thanks alot for any help or response

  9. Dear Sir,
    I have some complex problem, In below statement how i can pull the updated balance of any particular date

    Say for example, If my date input is 19/01/22, I should get the result in other cell as $1,300,189.93
    If my date input is 20/01/22, I should get the result in other cell as $1,243,874.99
    If my date input is 26/01/22, I should get the result in other call as $1,100,163.18

    Date Particulars Vch No. Debit Credit Blanace
    01-Jan-22 Opening Balance 1,280,656.09 1,280,656.09
    19-Jan-22 Local Purchase A/c for Trading PV-53919 1,951.00 1,282,607.09
    19-Jan-22 Local Purchase A/c for Trading PV-53920 1,646.00 1,284,253.09
    19-Jan-22 Local Purchase A/c for Trading PV-53921 4,988.01 1,289,241.10
    19-Jan-22 Local Purchase A/c for Trading PV-53922 1,890.00 1,291,131.10
    19-Jan-22 Local Purchase A/c for Trading PV-53923 2,910.40 1,294,041.50
    19-Jan-22 Local Purchase A/c for Trading PV-53924 856.80 1,294,898.30
    19-Jan-22 Local Purchase A/c for Trading PV-53928 5,291.63 1,300,189.93
    20-Jan-22 Local Purchase A/c for Trading PV-53929 17,284.91 1,317,474.84
    20-Jan-22 Local Purchase A/c for Trading PV-53930 535.50 1,318,010.34
    20-Jan-22 Local Purchase A/c for Trading PV-53932 25,864.65 1,343,874.99
    20-Jan-22 EMIRATES ISLAMIC BANK A/c.No 014390 100,000.00 1,243,874.99
    24-Jan-22 EMIRATES ISLAMIC BANK A/c.No. 014391 100,000.00 1,143,874.99
    25-Jan-22 Local Purchase A/c for Trading PV-54177 23,355.59 1,167,230.58
    26-Jan-22 Local Purchase A/c for Trading PV-54178 1,011.53 1,168,242.11
    26-Jan-22 Local Purchase A/c for Trading PV-54180 17,003.27 1,185,245.38
    26-Jan-22 Local Purchase A/c for Trading PV-54181 1,156.68 1,186,402.06
    26-Jan-22 Local Purchase A/c for Trading PV-54182 15,552.81 1,201,954.87
    26-Jan-22 EMIRATES ISLAMIC BANK A/c.No.014392 100,000.00 1,101,954.87
    26-Jan-22 Local Purchase A/c for Trading PV-54174 1,791.69 1,100,163.18

    Kindly help me to fix this requirement

    With regards,

    Sunil Pinto.

      1. Sorry Sir,

        I thought it two different sites. My apologies. You solved my problem.

  10. How I get the latest previous date of the today. Below is my query.

    My today is 2/11/22. and latest previous date of today in the string is 30/10/2022

    How I Can design the function.

    01-10-22
    05-10-22
    07-10-22
    09-10-22
    18-10-22
    21-10-22
    26-10-22
    30-10-22
    05-11-22
    08-11-22
    12-11-22
    14-11-22
    16-11-22

    thanks & regards,

    Sunil Pinto.

      1. Super Sir,

        Thanks a lot, your function is solved my problem.

        Thank you very much for your prompt response as well

        1. Sir, Above function will not work if my today is 4/11/2022 and I must get the answer of previous day of today is 30/10/2022

          But if i put the function as "Vlookup(Date(2022,11,4),A1:A14,1,Ture) I get the answer as 04/11/2022 and its wrong answer.

          Please help me

          01-10-22
          05-10-22
          07-10-22
          09-10-22
          18-10-22
          21-10-22
          26-10-22
          30-10-22
          04-11-22
          05-11-22
          08-11-22
          12-11-22
          14-11-22
          16-11-22

          With regards,

          Sunil Pinto

          1. Hi!
            Dates in Excel are numbers. Subtract from the date you are looking for a number less than 1. VLOOKUP looks for the nearest smaller number. Please read the information in the link I gave you earlier.

            =VLOOKUP(DATE(2022,11,4)-0.0001,A1:A14,1,TRUE)

  11. Challenge!!!!

    I have have two types of service 1000$ For 30 days and 1500$ for 30 Days, my Customer pays me 1000$ on 08-10-2022 and subscribe for 1000$ Package and at the end of the month on 31-10-2022 pays 1500$ and subscribe the other package.

    I want to know how much should I charge him on 31-10-2022 and 30-11-2022

  12. Hi, How do I use the If function to date range to bring back another date for example: Date date from: 30/06/2013 to 29/06/2014 brings back 30/06/2016, and Date date from: 30/06/2014 to 29/06/2015 brings back 30/06/2017, Date date from: 30/06/2015 to 29/06/2016 brings back 30/06/2018, Date date from: 30/06/2016 to 29/06/2017 brings back 30/06/2019, etc. and Date date from: 01/02/2019 to onwards - brings back exactly 2 years, or see below:

    Date ranges:

    From To brings back 1 & brings back 2
    30/06/2013 29/06/2014 30/06/2016 30/06/2020
    30/06/2014 29/06/2015 30/06/2017 30/06/2021
    30/06/2015 29/06/2016 30/06/2018 30/06/2022
    30/06/2016 29/06/2017 30/06/2019 30/06/2023
    30/06/2017 29/06/2018 30/06/2020 30/06/2024
    30/06/2018 31/01/2019 30/06/2021 30/06/2025
    01/02/2019 onwards 24 months 6years

    1. From To Bring back 1 Bring back 2
      30/06/2013 29/06/2014 30/06/2016 30/06/2020
      30/06/2014 29/06/2015 30/06/2017 30/06/2021
      30/06/2015 29/06/2016 30/06/2018 30/06/2022
      30/06/2016 29/06/2017 30/06/2019 30/06/2023
      30/06/2017 29/06/2018 30/06/2020 30/06/2024
      30/06/2018 31/01/2019 30/06/2021 30/06/2025
      01/02/2019 onwards 24 months 6 years from date

      Examples of dates Bring back 1 Bring back 2
      11/08 2013 30/06/2016 30/06/2020
      01/12/2017 30/06/2020 30/06/2024
      01/11/2022 01/11/2024 01/11/2028

  13. I need to enter a formula that involves two columns (one is a date column and the other column has a set dollar amount of $25.00 IF a date was entered into the first column), I assume this would be done using the IF function but I cannot figure out the proper way to enter it. Can anyone offer assistance?

  14. How can I display a null or blank value in a cell when I use year() function? because there will be cases where one of the selected cells for year() function doesn't have a date.

  15. Good day!

    May I ask for an assistance on how to solve the problem below

    In a sheet, dates are as follows:
    1. 1981/12/15
    2. 1978/12/02
    3. 01/24/2003
    4. 08/18/1980
    5. 05/25/1983
    6. 1981/03/23

    but the moment, i change the format to "YYYY/MM/DD" numbers 3-5 weren't change.
    What should I do in order for the dates to be in the same format?

    Thank you

  16. Hello, I have 7 cells with the days of the week populated, 28, 29, 30, etc... I want to use the first cell as a starting point for the other 6 cells and retuning that day of the week. So A1=28, A2=29. I just did a simple A2=A1+1 formal, but if a month has 31 days, I get 32, 33, or 34 if A1=30 or 31. I'm a little stuck. Thanks

  17. Hi, How do I calculate a future year and date, but the year must change to 3 years, but the date must always bring back 30/06/future year. Example: 10/04/2012 must return 30/06/2015. The month and date must always be 30/06. Thank you.

      1. Thank you very much. I have been battling with this, and you made it so easy. I appreciate your help

        1. Hi, can I have two arguments for the same sell - I would like to use =DATE(YEAR(C2)+2,MONTH(C2),DAY(C2)) and =DATE(YEAR(C2)+3,6,30) together, For Example if the year is 2015, then it must first check if the date is before 2018 to apply =DATE(YEAR(C2)+2,MONTH(C2),DAY(C2)), but if its after 2018, (01/03/2019 - then its should apply =DATE(YEAR(C2)+3,6,30) - basically having both formulas in 1 sell?

          Thanks in advange

          1. Hello!
            You can use the IF function to use different formulas depending on the year.
            For example,

            =IF(YEAR(C2)>2018, DATE(YEAR(C2)+3,6,30), DATE(YEAR(C2)+2,MONTH(C2),DAY(C2)))

  18. Hi,

    I have a timeline with moving dates, what I'm trying to do is to develop a formula that is able to determine how many days are on specific quarters of a year so for example:

    This study will have a start date: Feb 14 2022, and a finish date: May 17 2022
    The study cost was $3000

    Considering the previous info, I want to know how many days this study has on the 1st, 2nd, 3rd and 4rd quarter of the year so that I can then split the total cost of the study per each quarter.

    The quarters of the year are always de same: 1st quarter is Jan-Feb-March, the second is April-Jun-Jul and so on.

    Regards,

    1. Hello!
      Your request goes beyond the advice we provide on this blog. This is a complex solution that cannot be found with a single formula. If you have a specific question about the operation of a function or formula, I will try to answer it.

  19. Hello, i want to print to excel the dates between 1/1/2020 and 31/5/2022 but every day has to be printed 24 times. I mean ,
    1/1/2020
    1/1/2020
    1/1/2020
    1/1/2020
    1/1/2020
    1/1/2020
    1/1/2020
    1/1/2020
    .
    .
    .
    .
    .
    .
    .
    2/1/2020
    2/1/2020.
    .
    .
    .
    Any help please?

  20. Hi, I would like to know the formula on how to set up specific dates with a pattern, like for example,

    08 August 2020
    10 August 2020
    12 August 2020
    15 August 2020
    17 August 2020
    19 August 2020

    and repeats the process up to present day. Your response is very much appreciated. Thank you!

      1. Hello Alexander, thank you for the reply. Allow me to emphasize my example. The pattern is every Monday, Wednesday and Saturday only, and those are the dates that fall within those days. I'd like to know the idea behind it to ease my task of filling the rest of the dates up to present. Hope you could provide answer(s). Thank you.

        1. Hi!
          Use the WEEKDAY function to determine the day of the week. Copy the formula from cell A2 down the column.

          =IF(OR(WEEKDAY(A1,2)=1,WEEKDAY(A1,2)=6),A1+2,A1+3)

          Hope this is what you need.

  21. Hello,

    Very Complex question here I believe and I'll try my best to explain the situation. In short Im trying to figure out how to return a value of a widget in a separate column, of adjacent cells, when I select a given date from a drop down list while also being able to toggle between any other date to return the value of the widget based on that date that is selected. I update the value of the widgets manually every monday and they are listed vertically in values from F5:F479. The dates are arranged horizontally from F4:XFD. So as i update the values every monday the values change in columns by 1 every time (which represents 1 week basically) but im just adding to the previous weeks list of values so that i can monitor the increase or decrease in those values over time. So column "A" contains the name of the widget, Columns "E" through the end of the entire worksheet contain the listed weekly values, Column "D" is the column I want to use to show me the value of the adjacent widget based on the date i select from the drop down list. I have defined names for certain cells and arrays to utilize index match functions as well, i just cant figure out how to combine all the different possible functions of excel to get this to work. Any help is greatly appreciated. Would love to set up a meeting even to go over this if at all possible. Thank you!!

  22. Hi all,

    I would like to have a formula where the end result (as a date) is 1 month after the value selected, but I want it to be corrected to FRIDAY of that week, every time.
    For example,
    the value selected is: 25MAY22
    the end result that we want: 24JUN22
    ===>25MAY22 + 1 month (22JUN22 - which is Wednesday) and to the Friday of that week --> 24JUN22

    Is that possible to do that as a formula?

      1. Hey Alexander,

        Thank you for you quick reply, and yes the formula worked!

        By 1-month, I actually meant 4 weeks;
        25MAY
        1 week later: 01JUN
        2 weeks later: 08JUN
        4 weeks later: 22JUN

        I hope this makes more sense, but regardless, thanks again.

  23. I'm not sure if this relates specifically to any of the month formulas, but I'm trying to calculate vacation accrual each month. How can I have a specific value added to a cell automatically on the first day of each month? So, add 15 hours every 1st of the month to one cell, to continually show my vacation balance. Thanks!

  24. I have 3 columns representing dates of vaccination (1st dose, 2nd dose, 3rd dose dates). I have another column with visit dates. I must compare dates of vaccination to visit date- to see how many doses they had prior to the visit date. How can i do this. some time the first dose can be blank as they are unvaccinated.

    1. Hello!
      If I got you right, the formula below will help you with your task:

      =SUM((A1:C1 < D1)*(A1:C1 < > ""))

      D1 - visit date
      A1:C1 - dates of vaccination

  25. Hi, is there a way to get the nth day based on a specified start month?

    Day 0 is May 1, 2022
    Day 1 is May 2, 2022, and so on.

    If I were to input Jun 15, 2022, can it provide me what Day it is?

    Thanks in advance!

  26. I am trying to figure out if there's a way to get a "live count" on a spreadsheet. For example, I deal with people with terminal illnesses. I'm trying to track their lifespan from the date of the diagnosis to see how long their lifespan is. I have it figured out once they pass away. Is there a way to have a formula that will update each day that passes from the "start date" (which is the diagnosis date)... but there is no end date yet?

    Example E1 is the diagnosis date 1/20/2022, F1 is blank because there's no deceased date yet, and G1 will show "5" because today is the 5th day. Then tomorrow, it will automatically roll to "6" for the 6th day. Is this even possible to do in excel?

  27. Hi guys, I need help with this.

    Trying to set today date into a cell with 25.12.2021

    I keep getting 12/25/2021 when I use TODAY()

    Any help?

      1. Hi Alexander,

        Can a formula do that without going to custom formatting?

        I'm trying to use a bot to set the text in the cell. This cell is also referenced by some other cells.

        If there is a formula for this I will really appreciate.

        1. Hi!
          I guess you haven't read the manual from the link I gave you. There is a paragraph: How to change date format in Excel. You don't need to apply conditional formatting.
          By the way, no Excel formula can change the cell format. This can be done either manually or using a VBA macro.

  28. When I try put a date inside of if, it's doesn't work.

    For example, put this formula: =IF(A2>"6/30/2039";"6/30/2039";A2)

    Could you see is there something wrong?

  29. I want to set up a tracker for completion of mandatory training. The training is due to be completed on induction and then every 3 years following but in a specific month. I am having trouble with rounding the year over where induction is, say October, and the training is due to be completed in January. For example, the training was completed on induction on 28/10/2021 and needs to be completed every 3 years following in January. So the next training would be due 28/01/2025 as otherwise it would be only 2 years and 3 months until the next training.

    I have developed the following formula for the Next Completion Due cell (where cell D3 is the date the training is completed):

    =IF(OR(D3=DATE(,1,),D3=DATE(,2,)),DATE(YEAR(D3)+4,1,DAY(D3)),DATE(YEAR(D3)+3,1,DAY(D3)))

    The problem I am having is that regardless of what month I put in the D3 cell, the year is not rolling over to the next year where the training is completed in March or later (still using the January example).

    So when I enter 28/1/2021 the next completion date using the above formula comes out as 28/1/2024, but when I enter 28/10/2021 the next completion date using the above formula also comes out as 28/1/2024 (and not 28/1/2025 as I am intending it to be).

    Any ideas on how to get this to work for me would be greatly appreciated.

    Thanks.

    1. Created a report due date calculator to track when a report is due. (It is due no later than 16 days from admit date, but by Wednesday of that due week...) I am getting all the correct returns, with the exception of Tuesday Admits should be the same due date as the Wednesday Admits (all others are calculating as they are required per the client).
      The formula I used is to generate my due date is: =IF(B15="","",B15+14-MOD(B15-4,7))

      Not even sure if it is possible, but hoping! this error in due date only occurs on that every other Tuesday date, because it gets a little longer grace period than the others.

      Admit Date Report Due
      12/6/21 12/15/21
      12/7/21 12/15/21 (this one needs to be 12/22)
      12/8/21 12/22/21
      12/9/21 12/22/21
      12/10/21 12/22/21
      12/11/21 12/22/21
      12/12/21 12/22/21
      12/13/21 12/22/21
      12/14/21 12/22/21 (this one needs to be 12/29)
      12/15/21 12/29/21

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

        =A2+16-WEEKDAY(A2+16,14)

        This should solve your task.

        1. This new formula worked perfectly! Thank you so much!

          1. Works perfect! I just added in the portion to "hide" the date when it is blank:
            =IF(B7="","",B7+16-WEEKDAY(B7+16,14))

            Thanks again!

  30. How to calculate commission

    Source :
    1)company
    2) ref

    If source Ref :
    Ref benefits
    1)01-07-2020 to 31-12-2020
    (1st ref benefit-2000)
    (2nd ref benefit -30000 and)
    (3rd ref benefits 40000)
    2) 1-1-2021 to 31 march 2021
    (1st ref benefit 25000)
    (2nd ref benefits 25000)
    ( 3rd benefit 25000)
    3) 01-04-2021 To 31-03-2022
    ( 1 ref benefits 50000)
    ( 2nd ref benefit 50000)
    ( 3rd ref benefit 50000)

    If source company benefits
    Brokerage 3%
    =value *brokerage commission

    Please help how to calculate in one column?

    1. Please help

      1. Hi!
        The information you provided is not enough to understand your case and give you any advice, sorry. Your question is not entirely clear, please specify.

  31. Hello Alex, just want to ask. How to know the difference between to dates in one formula.
    Ex Jan 2021 and March 2021
    or March 2021 and Jan 2021

    Tried using DATEDIF but having error for negative months. Thank you!

  32. Hi.. I need help. I have column A (hire date) and B(termination date)

    Hire date 01/01/2000
    Termination date 20/04/2021

    How i want to calculate number of day started from 01/07/2020 - until termination date?

    Appreciate your help

  33. Please what is the formula calculating. I know the first row is the difference between the two dates
    DATEDIF([Date Reported ],[Date Closed],DAY),
    DATEDIF([Date],TODAY()+IF(HOUR(NOW())>14,1,0),DAY)

  34. Hello,

    I am trying to return a payroll date based on a hire date for incentive. The incentive is paid after 2 months of employment. I can use EDATE to calculate the 2 month date, but need to have the result hit the corresponding pay date. We have 24 pay periods so our pay dates are on the 15th and the final day of the month. I need the result to return one of those two days based on when the day the 2 month anniversary is hit. i.e.
    Hire Date = 1/5/21
    2 months = 3/5/21
    Pay Date = 3/15/21

    but if that person was hired on 1/18/21, then they would be paid on 3/31/21. How can I get those pay dates to calculate automatically?

  35. Is there an MS Excel formula that determines the date/year of retirement after 60 years of age when the date of birth is known?
    I will be very grateful!

    1. Hello!
      Add 60 years to your date of birth as described in this article. You will receive the date you want.
      I hope I answered your question. If something is still unclear, please feel free to ask.

  36. How to find smallest of set of dates.

  37. I would like to be able to conditional format cells that are within 30 days of a date shown in a cell

    How would i do this?

    Many thanks

  38. I am looking to add a review date to a contract expiry date and convert it the month.

    For example: expiry date is 02/09/2021 and I need to set the review date 90 days before.
    I have =09/09/2021-90 and it returns 09/06/2021 but I would like to it to return June?

  39. Hi Alex,

    Im looking for a formula that escapes me at the minute, I have an invoice where the start date begins on the 21st of each month say 21/02/2021 in cell d3, in a column I have days in the week Sunday to Saturday cells a5:a11. In the formula I need the date to change based on the invoice start date, the 21st and the day in the week column a5:a11?

    So If cell a5, which is Sunday is the 21st cell a6, Monday would become the 22nd etc.

    I have done a formula like this before but it escapes me what the best way to do it is.

    Thanks in advance

  40. Can I conditionally format a column to let me know if the date I type in is a future date?

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

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

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

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

  45. 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?

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

  47. 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!!

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

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

  50. 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?

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