Comments on: Using DATE function in Excel - formula examples to calculate dates

The tutorial explains the syntax and uses of the Excel DATE function and provides formula examples for calculating dates. Continue reading →

Comments page 2. Total comments: 684

  1. Dear Sir

    Once again thank you so much.
    My meaning is how to supply the start date directly to the the formula, use the DATE function

    =IF(AND(B2=Sep/23 "Pass", C2=Oct/23"pass"), "Good!", "Bad")

    Submission date Sep/2023- Receiving comments Oct/2023- Last Rev. No. Good
    Thank you

  2. Dear Sir

    Thank you for your reply.
    My meaning is how to use with date.
    Submission date 10/Sep/2023- Receiving comments 01/Oct/2023- Last Rev. No. Good -Bad

    =IF(AND(B2=10/Sep/2023"Pass", C2=01/Oct/2023"pass"), "Good!", "Bad")

  3. Hello, I need help..

    But I'm not quite sure how to describe it. I wants to count the total numbers of working days based on days with figure > 1.

    Eg. Work day is Mon to Sat. And certain days, the fig 1-100. So instead of counting total numbers, I wants to count it as total working days.

    Mon = 20
    Tues = 40
    Wed = 0
    Thurs = 60
    Fri = 0
    Sat = 0

    So instead of 120, I wants to count it as 3 working days. How do I go about it?

  4. Dear Sir

    we need your support please.
    how to fix this formulas if i have 3 column Ro0.Ro1.Ro2
    if i will type date a cell that come to auto come Ro0 or Ro1 0r Ro2 or and i want just type date and come to

    =IF(F8,G8.H8<=TODAY, "RO0","RO1", "RO2"))
    Thank you

  5. Hello, I created an inventory sheet. I have the count of each item in colum b and my goal is to associate a date in colum d when i enter a value in colum b. Is this even possible

  6. I HAVE VALUES :
    TYPE Name Date Amount Date2 Amount3
    Pre ABDUL 01-03-2024 2000 03-03-2024 2001
    post KAREEM 04-04-2024 4000 05-04-2024 4001
    post Sharief 03-07-2023 3000 04-07-2023 3001

    I need to enter the date specific in return i need amount of that month how can I enter formula ? to fetch the current month amount

  7. what is the formula of getting the target completion date from the date received plus the SLA/TAT.

  8. How to compute the due date excluding weekends and holidays

  9. If i'm monitoring runoff on a porfolio and each month I go in and change payments made, the fomula I have automatically decreases the payments remaining. However the next column is "next due" date. What formula would I use to advance the date one month ahead when the "payments remaining" column automatically decreases?

    1. Hi! Unfortunately, this information is not enough to recommend a formula to you. Describe your task in more detail. Give an example of the source data and the expected result.

      1. Hi. The spreadsheet has a loan portfolio. It contains the original amount, the monthly payment, the # of payments made, the # of payments remaining, date of next payment due and the outstanding balance. I go in and update the # of payments made and everyother cell recalculates the data except the next payment due date. I don't know how to formulate the date column to change to the next month (the day stays the same) when the # of paymetns remaining automatically subtracts.
        cost payment term payments made remaining next due contract balance
        $60.00 $1.00 60 48 12 09/20/2023 $12.00

  10. what is the formula to get a result of 20th from a cell containing a August 20, 2023. thanks

  11. I have a date column that auto populates 30 days from a previous colun usign a sum function. I am trying to figure out or nest the workday function into this column so if the 30 day date falls on a sat or sun it would revert to the previous Friday date

  12. i have a date (mmm-yy) in Column A, Column D represents a number (1, 2 or 3) - I wish to write a formula for if column A shows a month/year, and column D shows number 1, new column should show a date 3 months into future.
    Number 1: 1 month into future
    Number 2: 3 months into future
    Number 3: 6 months into future
    Eg.
    Date: Jun-23
    Number: 1
    New Date: Jul-23

    Date Jun-23
    Number: 2
    New Date: Sep-23

    Date: Jun-23
    Number: 3
    New Date: Dec-23

    Thanks!

      1. Assigned date - completion date example= 15-09-23 - 13-09-23 answer required sir how many days take to completing production.

  13. Is it possible to combine dates in text strings to not repeat days, months, or years if both dates have them in common?

    Example:
    "15 Aug 23-15 Aug 23" would be "15 Aug 23" (day/month/year not repeated)
    "15 Aug 23-16 Aug 23" would be "15-16 Aug 23" (month/year not repeated)
    "15 Aug 23-16 Sep 23" would be "15 Aug-16 Sep 23" (year not repeated)
    "15 Aug 23-16 Jan 24" would be "15 Aug 23-16 Jan 24" (my formula renders this correctly as day/month/year are different)

    Here's the formula I'm currently using to get the first renderings:
    =TEXT(A1,"d mmm yy")&"-"&TEXT(B1,"d mmm yy")
    A1: Start Date
    B1: End Date

    Is this possible? I don't think my current formula is actually on the right path. Is there a way to make Excel return a date range (15-16 Aug 23) given a start date and number of days long (15 Aug 23, 2) or something? Since I have start and end dates, I can calculate length in days in a third column and then use that data in the correct formula in column 4.

    Thanks!

    1. Okay, so I was able to figure out a work around using IF() formulas after breaking out Month and Year in their own columns to compare against.

      A1: Start Date
      B1: End Date
      C1: Start Month
      D1: End Month
      E1: Start Year
      F1: End Year
      G1: =IF(D1>C1,TEXT(A1,"d mmm"&"-"),IF(A1=B1,"",TEXT(A1,"d"&"-")))&TEXT(B1,"d mmm yy")

      I'm sure there's a cleaner way, but this produces the outcome I want. I realized that the end date is always used, it's the start date that is modified depending on it's commonality to the end date. A one day event only needs to show the end date. An event spanning several days of the same month only needs the days from the start date. An event spanning two months needs the day and month from the start date but not the year. An event spanning two years needs the entire start date with the end date.

      This is probably the most complex thing I've done in Excel. I welcome any feedback!

      1. Sorry, copy/paste issues, here is the right formula text string, I also put the logic questions in a logical order:

        A1: Start Date
        B1: End Date
        C1: Event Name
        D1: Start Month C1
        E1: End Month D1
        F1: Start Year E1
        G1: End Year F1
        F1 =(IF(A1=B1,(TEXT(B1,"d mmm yy")),(IF(E1=D1,(TEXT(A1,"d")&"-"&TEXT(B1, "d mmm yy")),IF(F1=G1,(TEXT(A1,"d mmm")&"-"&TEXT(B1, "d mmm yy")),(TEXT(A1,"d mmm yy")&"-"&TEXT(B1,"d mmm yy"))))))&": "&C1)

  14. Hi,

    I am looking to create a formula that will take the date from one cell (say it is cell B2, data is 08/02/2023) and in a new cell, have an IF statement that would produce the following:

    If the date in cell B2 is less than the date in G2 (which is currently a formula of if(isblank(B2),"",B2+30) the new cell would be labeled as "On Hold" but if the date in B2 is greater than the date in G2 the new cell would be labeled as "Past Due".

    This would need to be two arguments, but I am killing myself trying to figure it out and haven't been able to find the correct way to write the formula.

    Any help would be appreciated.

    Thank you!

  15. I want to convert 11.50 to years months and days, ex: 11.50 is 11 years 6 months and 0days

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

      =INT(A2)&" years "&INT((A2-INT(A2))*12)&" months "&INT(((A2-INT(A2))*10-INT((A2-INT(A2))*10))*30)&" days"

      For more information, please visit: How to round numbers in Excel.

  16. I am trying to create a clock calendar counter in excel.

    if i have one column start date 7/1/203 the next column want an auto set counter to count 21 days from prior date then next column to count 30 days from previous date and last count 90 days from previous date

    Thank you

  17. how to sum total in excel output same format in excel
    1 years, 9 months, 26 days
    1 years, 8 months, 25 days
    0 years, 3 months, 11 days
    0 years, 5 months, 0 days

  18. I am trying to set a formula for days counter
    To subtract a date from today's date: =TODAY()-DATE(2015,5,20) The formula calculates how many days are between the current date and some other ...
    I use a date entered in separate column -- say "H"
    so result "L" = (TODAY()-(Hxx)) where xx is the line-number
    but I need to use a condition as follows:
    if value in column "K" is null or non-numeric (as text), then "L" = (TODAY()-(Hxx)), otherwise "L" = (TODAY()-(Kxx))
    i.e. I need to use a date from column "H" or column "K", depending on - whether the date is entered in column "K". But if column "K" is blank or has some notes (optional), then the conditional formula should use value of the column "H".
    Could anyone suggest that type of conditional formula?
    Thank you

      1. Great, Alexander !
        Thank you very much !! it works well.

  19. December, 21 2022 Date Change Formate 21/12/2022

  20. Hello, I need your help to get a formula to calculate below,

    below are the dates and holidays. Please recreate the formula to get exact working hours on monthly.

    Conditions : -

    23 March to 1o April - 6 working hours per day
    11 April to 19 April - 5 working hours per day
    23 march to april 19 - 3 working hours on friday
    Rest of the working days - 8 working hours and 6 working hours on friday

    Date Holidays
    January 20-Apr-23
    February21-Apr-23
    March 27-Jun-23
    April 28-Jun-23
    May 29-Jun-23
    June 30-Jun-23
    July 01-Dec-23
    August
    September
    October
    November
    December

  21. Can you correct a date where the month was incorrectly entered as a year??
    For example:
    Jan 1, 2023 was entered as '2001-1-23'

    How can I correct it to read '01/01/23'

    1. Split this value into text strings with "-" delimiters using the TEXTSPLIT function. Use these in the DATE function to get the date.
      You can find more information about the CHOOSECOLS function in this article.

      =DATE(CHOOSECOLS(TEXTSPLIT(A1,"-"),1), CHOOSECOLS(TEXTSPLIT(A1,"-"),2), CHOOSECOLS(TEXTSPLIT(A1,"-"),3))

      I recommend paying attention to the Convert Text to Date tool. This simple tool easily converts text strings of different formats to dates without formulas. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.

  22. Hi

    Relatively easy one (I hope!) - i just need to calculate the salary due to a leaver based on the days in the month.

    If a person is leaving, they receive their salary / 12 / days in month of leaving * how many days up to their leave date. So someone leaving on 19th March would receive 30000 / 12 / 31 * 19 = 1532.26

    We have the salary and date of leaving in separate columns, how do we put in a formula to do the 1532.26 using the salary and date of leaving columns?

    Thanks for help! : )

      1. Thanks for this and I can see the benefit and will use in the future.

        But what if I wanted to calculate the salary due on the actual leave date as opposed to todays date:

        30000 19/03/2023 Formula to return 1532.26

        Many thanks

        1. Hi! Instead of the TODAY() function, use the DATE function to determine the target date. You can find more information about this function in the article above.

        2. Don't worry, I've used the basis of your formula and inserted the column references and it now works fine! : )

          Many thanks again

  23. Hi,
    Is there a formula for turning dates and events as data in an excel spreadsheet into an excel calendar template.
    Thanks for your help

  24. Date 30-11-22

    Need FY 2022-2023

    What is the function with formula ??

  25. Is there a formula that combines the vlookup function (for invoice due date terms) and due date based off invoice date? I have a formula that automatically adds 30 days to the invoice date to get the due date, but a handful of customers have different terms (45, 60 and 90 days) that I have in a separate table. I want to take the invoice date, use the vlookup function on the customer's name to get the terms and then get an invoice due date. Is this possible?

  26. Is there a formula to extract the date from a cell containing "Wed Feb 22 2023 00:00:00 GMT-0500 (Eastern Standard Time)" and convert it to mm/dd/yyyy?

    Thanks in advance!

    1. Hello!
      You can use MID function to extract the desired parts of the text string and use them in DATE function.

      =DATE(MID(A1,12,4),MONTH(1&MID(A1,5,3)),MID(A1,9,2))

      To convert text to date, you can use this Excel tool. With Ablebits Text to Date tool, users can quickly convert dates, including those with different separators (dots, dashes, slashes, etc.), correctly interpret ambiguous dates or dates in various formats, and format resulting dates according to their needs. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.

    2. Hi!
      Вы можете тимпользовать функцию MID чтобы извлечь нужные части текствов

      =DATE(MID(A1,12,4),MONTH(1&MID(A1,5,3)),MID(A1,9,2))

  27. Dear Alexander

    I have a list of task with different dates.
    I want to get e text "due in first week", due in 2nd week, due in 3rd week, depending upon the due date.
    Would appreciate your input on this

    Rgds
    Louis

  28. =IF(TODAY()=DATEVALUE("2023-02-07"),INDEX(PLAYING!$E$1:$G$200,MATCH(DE4,PLAYING!$E$1:PLAYING!$E$200,0),2))
    This formula works great except, once it executes on the given date it gives me the correct answer. But once the date has passed it changes the answer to FALSE. Some how I need to retain the answer that was given on the exact date of execution. I have been copy, paste, value to retain the correct answer. But was looking at anyway to keep the correct answer with out extra steps.
    Any help to point me in the right direction would greatly be appreciated.
    Thank You

    1. Hi!
      If I understand your task correctly, you are replacing the TODAY function in the formula with a reference to the cell where the desired date should be written. However, in order to get results on a specific date, your table must contain a column with dates. In this case, I assume there is no such column.

  29. I am looking for a few formulas for the following.
    - I have two start dates (Original and the a start date for the fiscal year), i have a formula that will calculate the current time in program in years/months based on the original start date.
    -I have another formula that will calculate the total amount of time in program based on original start date and end date.

    I would like to know if there is a formula available to ensure that the current amount of time in program will stop calculating to the current date if the 'end date' is entered.
    I would also like to know how to get an average formula in months based on my column for current time in program for the table.
    Please help.
    Thank you

  30. Hi there I"d love some help with either excel or google spreadsheets for enrolments for school.
    I'd like to start with DOB (date of birth DD/MM/YYYY) + 5 years and then If the month is jan/feb/march/april then the result is 1 and if the month is may- dec then the result is 0

    Example: 21/09/1966 + 5 years 21/09/1971 if the month is sept then the result is year 0

    Hoping you can help

  31. Hi I am trying to work on a budget and If I have a yearly budget of £100 000 for example then I should spend £8333 or less each month.
    What I want to do however is use a formula that will keep updating as I enter in a new date each month.
    If I only spend £8000 in January then I want the leftover £333 to be split up by the months remaining (11) and a equal portion given to each month, does this make sense? So the monthly budget keeps updating in line with spending, same with if I spend over budget, I want the amount I have overspent to be divided by the months remaining and an equal portion taken off each month.
    I have a whole load of departments so this would take ages if I did it manually, what I want to do is input my spend for the month in each department and enter in the date (31/01/2023) and all the budgets update, hope this makes sense, would appreciate the help

  32. Hi Sir,
    I am trying to pickup date from below data. Can you please help me . I dont know which formula i have to use with. i have 18 thousand rows to pickup the dates. Thank you .
    Security: Test ObjectIAH10141/19/2023Tier 2 - Catering Cartfront
    Security: Test ObjectIAH10241/19/2023Tier 2 - Catering Cartfront
    Security: Test ObjectIAH10671/18/2023Tier 2 - Catering Cartfront
    Security: Test ObjectIAH4451/5/2023Tier 2 - Cabin CompartmentN/A
    Security: Test ObjectIAH11841/17/2023Tier 1 - Seatback Pocket3e
    Security: Test ObjectIAH14641/29/2023Tier 1 - Seatback Pocket34C
    Security: Test ObjectIAH13691/21/2023Tier 1 - Seatback Pocket3B
    Security: Test ObjectIAH10841/28/2023Tier 1 - Seatback Pocket39c
    Security: Test ObjectIAH13811/23/2023Tier 1 - Seatback Pocket5B
    Security: Test ObjectIAH20931/25/2023Tier 1 - Seatback Pocket39c
    Security: Test ObjectIAH10161/25/2023Tier 1 - Seatback Pocket37c
    Security: Test ObjectIAH20671/30/2023Tier 1 - Seatback Pocket34C
    Security: Test ObjectIAH62261/28/2023Tier 1 - Seatback Pocket7C
    Security: Test ObjectIAH10341/25/2023Tier 1 - Seatback Pocket1e
    Security: Test ObjectIAH13811/29/2023Tier 1 - Seatback Pocket20E
    Security: Test ObjectIAH10141/15/2023Tier 3 - Water Heaterfront
    Security: Test ObjectIAH16751/11/2023Tier 1 - Seatback Pocket3B
    Security: Test ObjectIAH20671/18/2023Tier 1 - Seatback Pocket3B
    Security: Test ObjectIAH22141/12/2023Tier 1 - Seatback Pocket3B
    Security: Test ObjectIAH14391/12/2023Tier 1 - Seatback Pocket3e
    Security: Test ObjectIAH10241/4/2023Tier 2 - Catering Cartfront
    Security: Test ObjectIAH10021/12/2023Tier 1 - Seatback Pocket4E
    Security: Test ObjectIAH5181/2/2023Tier 2 - Cabin CompartmentN/A

    1. Hello!
      Your data has no general pattern and is 9 or 10 characters long. Also, the text before the date is of different lengths. Unfortunately, I cannot help you.

  33. Can this formula be used to always return the following 31 March from another date eg if I start with 1 April 2017 or 1 January 2018 is there a formula to return 31 March 2018 regardless of what the start date is?

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

      =IF(A1>DATE(YEAR(A1),3,31),DATE(YEAR(A1)+1,3,31),DATE(YEAR(A1),3,31))

  34. Hi i want to calculate below duplicate excel dates only one time. ans should be total 8 days please suggest is his possible.
    01-Jan-22
    02-Jan-22
    03-Jan-22
    04-Jan-22
    05-Jan-22
    06-Jan-22
    07-Jan-22
    08-Jan-22
    04-Jan-22
    05-Jan-22
    06-Jan-22
    07-Jan-22
    08-Jan-22

  35. Hello - Using Excel 365 I am trying to create a yearly planner workbook with separate sheets for each week. I have a start date in cell J5 of my first week sheet [Week1], I have then created a simple formula [=J5+1] for the next day, which copies out nicely for the remaining days of the week. It works great for the one sheet, but the next sheets [Week2, Week3, Week4, etc.] would need to look to the last day cell from the previous sheet to continue the dates. Example, Sheet [Week2] cell J5 would use [Week1] J29+1. I can make this work manually, but I would like to be able to copy the remaining weekly sheets of the year and have the formula update dynamically. Is this possible?

  36. Hi, I have problem substracting for remaining days for large dates. For example, I want to calculate remaining days from date today 5-Dec-2022 until 24-Jan-2030, I used the simple formula "=delivery date - today()" but it appear "0" which is not correct. What is the formula for this kind of case. Please help me! Thank you.

  37. HOW CAN I USE A FORMULA PICK UP THE DATES FOR TUESDAYS AND THURSDAYS ONLY IN A MONTH

  38. Excel formula If Date is between July 1 2019 - June 30 2020 enter Fy20 and If between July 1 2020 - June 30 2021 enter FY21 and If between July 1 2021 - June 30 2022 enter FY22

  39. Hi Brains trust,

    I am using "online google Sheets" version of excel
    And have created check boxes to track production of components.
    I have used this formula =if(H3,Today()) in a seperate cell to track the date the check box was done, however, when a new check box is chosen along the process it changes all the existing dates to todays date also. Does anyone know how I can adjust the formula to keep the date each check box was ticked?

    Thanks for your help

  40. i have 1 year water collection data. every 15 minutes interval data is recorded. it means every day 96 cells data is recording into excel. some times it will be missing 1 or 2 days of data. How can i identify directly in excel (missing data in particular time or data)

  41. Hello, I have the following data: Cities, order creation dates and order confirmation dates. I need to count the number of orders in each city with the same order and confirmation date. I'm counting with the countifs function, but I can't figure out where the problem could be, maybe I'm putting the function together wrong?

      1. I have tried many ways but I am not getting any result. How should the function look like to calculate how many values ​​there are when the creation date and the verified date is the same:

        Column A - City name, Column B - Creation date, Column C - Verified date, my function look like this: =COUNTIFS(A:A,G16,B:B,"=C2")

        G16 is the city name for which all values ​​satisfying the condition must be counted. The date format is Short date.

        1. Hi!
          Look closely at the syntax of the COUNTIFS function. The = sign is not used in function arguments. Also take a look at the link I gave you earlier.

          =COUNTIFS(A:A,G16,B:B,C2)

  42. Hi,

    I used the Excel DATE function to calculate dates for example (=DATE(YEAR(D4)+1,MONTH(D4),DAY(D4)) so when I enter say 1/1/2022 the next cell will automatically say 1/1/2023

    Can I auto fill the formula to a blank cell? When I auto fill it to a blank cell a date (1/1/1900) appears. I want the cell to remain a blank cell but still have the formula in it so when someone enters a date the next dates will automatically appear. So if I enter say 1/1/22 into one cell 1/1/23 will appear next to it and so on.

    Is this possible or will I have to put up with 1/1/1900 until I enter a date into the cell?

    1. Hello!
      Use conditional formatting in a cell with a condition formula

      =D4=""

      Set a custom number format in conditional formatting to not show zero: #,##0;-#,##0;
      Add a condition to your formula:

      =IF(D4="","",DATE(YEAR(D4)+1,MONTH(D4),DAY(D4)))

      I hope I answered your question. If something is still unclear, please feel free to ask.

  43. Sir I have little complex requirement

    In my below statement of account I need to pull the closing balance of any particular date how it will be possible, please help

    below is my example

    Date Description Debit Credit Balance
    05/11/2022 Opening balance 6,200.00 6,200.00
    05/11/2022 Paid to Raju 230.00 5,970.00
    05/11/2022 Received from Vijay 550.00 6,520.00
    06/11/2022 Cash Collection 8,345.00 14,865.00
    06/11/2022 Food Expenses 258.00 14,607.00
    06/11/2022 Travelling Expenses 879.00 13,728.00
    06/11/2022 Paid to Stanly 980.00 12,748.00
    07/11/2022 Trafic Fine 85.00 12,663.00
    07/11/2022 Cash Collection 3,330.00 15,993.00

    In cell my date is 6/11/2022 and in cell i need the result of closing balance of 6/11/2022, that is 12,748.00

    How I can pull the closing balance from statement of account of any particular date

    Please help me.

    With regards,

    Sunil Pinto.

  44. Hello, I am trying to extract just the date from this format: 2022-01-01 13:23:59 +0000

    I have tried to classic formulas but they don't seem to be recognising it. I have over 2,000 rows of data and so I need a quick fix to extract date only please?

    Thank you!

  45. .. lll
    How I can get a different information of
    different employees in different date in one
    cell?

  46. I need a condition like previous month of 9th to current date is this possible in excel and every time previous month will be changed like this is october then the previous moth is september if the month is december then need to extract data from november 9th to current date

  47. When using the formula =datedif(A4,B4"m") the formula shorts me 1 month, example if I entered 01/01/2023-12/31/2023 the formula says that is 11 months not 12, how do I correct this?

    1. Hi!
      It has been said many times here that 12/31/2023 means 12/31/2023 00:00:00. Therefore, the date 12/31/2023 is not included in your time slot.

  48. Hi,
    I need to set up formula, that shows if "start date" less than 3 days from TODAY and show me urgent alert.
    thank you,
    S

  49. how can i sum a column with a date range like for month of September and for one particular name(like how much bob spent in month of September 1 to 30th)

  50. Sir i need one help, exm: I have one date (20-Sep-2022) in A2 column, in A3 column i write down number 66 (days), i need date result after 66 days on A4 column. can you please help me this unction.

    A2 A3 A4
    20-Sep-2022 66 (show date after 66 days)

    Thanks and regards
    bdmauz

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