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 3. Total comments: 372

  1. I am trying to figure out a formula for the following:

    If anything in (A) column=5, then take dates in (B) column And add 14 years, 1 month and subtract 1 day, And if anything in (A) column=4, then take dates in (B) column And add 8 years, 1 month and subtract 1 day.

    Any help would be appreciated

  2. Hello, I need the formula which will produce a future date when I input a number of weeks
    Column A: Date
    Column B: Lead time (number of weeks)
    Column C: I need the future date in here

    Thank you!

  3. I need a formula to look at two different dates in two cells and choose the latest date of those two to then add 26 weeks to and come up with a new date.

  4. Hello
    Sir,
    Please tell us formula date to 09/04/2020 dd/mm/yyyy

  5. Buna ziua

    Tot incerc sa aranjez un tabel in ordinea cronologica a datei calendaristice si nu reusesc.
    Cum pot sa rezolv aceasta problema?
    Am incercat multe variante si degeaba.
    Cu multumiri

  6. Good day
    I need a formula to help me with Invoicing payment dates. So if I receive an invoice on the 20th or before that, the invoice needs to be paid end of the following month (eg Invoice date - 20-Nov-20 and payment will then be 31-Dec-20), but if an invoice is received after the 20th of a month, it needs to be paid 2 months later (eg Invoice date - 22-Nov-20 and payment will then be 31-Jan-21)

    Thank you in advance

  7. I want to create cells that adds a range of days to dates with parameters: not to be less than the first day of the next month and not to exceed the last day of the next month. I enter a date in one cell, next cell adds 28 days but at least first day of next month, third cell adds 35 days not to exceed the last day of the next month.

    1. Hello!
      Formulas that calculate the first and last day of the next month

      =EDATE(D3,1)-DAY(D3)+1

      =EOMONTH(D3,1)

      I hope it’ll be helpful.

  8. Hi
    I need to set a formula in excel so when one cell is updated with a month and year (e.g. Jan 20) the cell below it automatically updates to 3 months later (e.g. April 20). Does anyone know how to do this please?

  9. i have this formula =IF(O5"",O5-B5,Q2-B5) and when i drag it down it will be like this =IF(O6"",O6-B6,Q3-B6) how can keep the Q2 when dragging

  10. Hi,
    I want to make a spreadsheet of cut off dates which will keep changing every year as the sheet is opened. How will I get this? Whats the excel formula?

  11. I have a spreadsheet with completion dates. For Example:
    2/21/19
    5/24/20
    11/28/19
    I need to calculate the percentage of how many I have that are less than 365 days. How would I go about doing this?

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

      =YEARFRAC(DATE(YEAR(A1),1,1),A1)

      I hope my advice will help you solve your task.

  12. Hi. I am trying to calculate future dates from today using a given date in the past such as given date = 6/1/20, today 7/6/20, the fixed day of month (excluding weekends and holidays) is the 1st of each month so I need my formula to tell me the next date from 7/6/20 (in this situation would be 9/1 then 10/1, etc) and to continue for a given period of time. I have the following formula: =WORKDAY(EDATE($B$6,2)-1,1,Sheet2!B1:B14) but it will give me the next month based on todays date. Help please

    1. Hello Angela!
      If I understand your problem correctly, you want to determine the first working day of each next month. The formula below will do the trick for you:

      =WORKDAY.INTL(EOMONTH(A1,0),1,1)

      I hope this will help

  13. I need assistance please. I am working on a spreadsheet with a Surname in Column D; Admission date I; Discharge date K; Date back at work L; Deceased Date M; Healthy/Deceased N;
    My formula: =IF(D3="","",IF(N3="Deceased",(_xlfn.DAYS(M3,I3)),(IF(L3="",(_xlfn.DAYS(TODAY(),I3)),_xlfn.DAYS(L3,I3))))) So it means if there is a Surname in D, Look in M if deceased; if deceased calculate Deceased date, if not deceased calculate the Days off sick. What am I missing or doing wrong? I only get the #NAME? Back.

    1. Hello!
      I noticed the smart quotes in your formula and I suppose they are the cause of the error. Please convert your smart quotes into the straight quotation marks, that should fix the problem. Also, you may check this Microsoft manual to learn how to disable smart quotes: https://support.microsoft.com/en-us/office/smart-quotes-in-word-702fc92e-b723-4e3d-b2cc-71dedaf2f343
      Check the formula. I have this formula

      =IF(D3="","",IF(N3="Deceased",(DAYS(M3,I3)),(IF(L3="",(DAYS(TODAY(),I3)),DAYS(L3,I3)))))

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

  14. contract start 01-10-2019
    contract end 31-08-2020
    how many days remaining for renewal

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

      =C2-TODAY()
      or
      =DATEDIF(TODAY(),C2,"d")
      C2 --- 31-08-2020

  15. Hello I am unable to change date format from MM/DD/YYYY to dd/MM please help me

  16. I am trying to make a chart with conditions that applys a date plus a number of days if a cell states yes in a new cell. I cant think of a proper formula. Can anyone help?

  17. I want to create remarks that denote that a particular activity is due in certain days (based on the last date when it was performed, mentioned in the sheet) or a particular activity has passed *this* number of days past its due date (also mentioned in the sheet)
    The sheet has current date, activity performed date and due date in columns. Kindly help

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

      =IF(B1="","Performed in "& (C1-A1) & " day", (A1-B1) & " days ago")

      If there is anything else I can help you with, please let me know.

  18. I'm trying to look at a priority level = A (+2 days), B (+5 days), C (+10 days) or D (+20 days) (in column E) then add the corresponding number days (+# days to column K) from the date in column C. Example in column C I have "01 Jan 20" and in column E I have "B" therefore I want column K to automatic insert "06 Jan 20" which is the +5 days due to priority level "B" selected. Thank you in advance for your assistance.

    1. Hello Ken,
      Please try the following formula:

      =C1+CHOOSE(MATCH(E1,{"A","B","C","D"},0),2,5,10,20)

      I hope it’ll be helpful.

  19. Dear , I seek your help to change the date 12/01/2019 to 12012019 format please..
    it would be great help

    1. Hi Mohammed,

      The easiest way is to apply a custom date format to the cell. Here's how:

      1. Select the cell(s) you want to format.
      2. Press Ctrl+1 to open the Format Cells dialog.
      3. On the Number tab, select Custom from the Category list and type one of the below format codes in the Type box.
      4. Click OK to save the changes.

      If 12 is the month and 1 is the day, use this format: mmddyyyy
      If 12 is the day and 1 is the month, use this one: ddmmyyyy

      For more information, please see How to create a custom date format in Excel

  20. Adding days to a date:

    =DATE(2015, 5, 20) + 15

    The formula adds 15 days to May 20, 2015 and returns June 4, 2015.

    my question is:

    how i want add 15 workdays(exluded sunday and saturday).. help me

    1. Hello, Arman,
      Please try the following formula:

      =WORKDAY.INTL(DATE(2015, 5, 20), 15, 1)

      You can learn more about calculating weekdays in Excel in this article on our blog.

      Hope you'll find this information helpful.

  21. 25/10/2018 how to make appear like 25 Oct 2018 in excel cell
    Normally 10/25/2018 is appear as 25 Oct 2018 in excel cell.

    1. Suthan:
      You can modify how the numbers and dates appear in the cells by changing the cell's formatting.
      Right click on the cell select Format Cells then Date and choose a date format from the list then click OK.

  22. How to get the formula of 30 DAYS OVERDUE if the given is date today, terms and due date ?

    1. Steve:
      What are the terms and due date?

  23. I am working on a spreadsheet that I am using the following formula. =COUNTIFS(REWORK!C:C,"BOM Error",REWORK!A:A,"=12/17/17") How can I incorporate the date function into the formula to avoid having to try to change for each line?

    1. =COUNTIFS(REWORK!C:C,"BOM Error",REWORK!A:A,"=12/17/17")

  24. DEAR,

    IF I HAVE 03 CYLINDERS CY 01, CY 02 & CY 03 IN STORE. I WANT TO TRACK WETHER THE SAID CYLINDER IS FOR HYDRO TEST OR IN O.K. CONDITION. THE CYLINDER SHOULD BE HYDROTESTED AFTER 06 YEARS FROM THE DAY OF MANUFACTURING OR HPT TEST.

    E.G.
    CY 01 (01.01.2016) THE RESULT SHOULD COME O.K.

    CY 02 (06.01.2005) = FOR HPT (BECOZ THE CYLINDER HAS PASSED 06 YEARS FROM THE DATE OF MANUFACTURING & SHOULD BE HYDRO TESTED)

    CY 03 (05.07.2009) FOR HPT

    THANKS IN ADVANCE

    1. Shams:
      Here is how I set this up:
      CYNum Date Tested Current Date Result
      CY 01 1/1/2016 5/4/2018 OK
      CY 02 6/1/2005 5/4/2018 HPT
      CY 03 5/7/2009 5/4/2018 HPT

      CYNum is in A
      Date Tested is in B
      Current Date is in C
      Result is in D
      Notice each variable is in a separate column and cell.
      Notice date is in M/D/Y format.
      Current Date in each D cell is function =TODAY()
      In Result cell D2 for 1/1/2016 the formula is:
      =IF(DATEDIF(B2,C2,"Y")>=6,"HPT","OK")

  25. Dears,
    I have a difficult question.
    I have a duplicated codes with different receipts dates.
    How can I get the latest date for each code

    1. Shady:
      The simplest method would be to sort the data by date.
      Can you try that?

  26. plz help
    when in cell a1 have only year and b1 have complete date, how can we use subtract formula for it.
    for example
    a1 b1 Result of subtraction
    1967 07/03/2018 ?

    1. Hello Hayat Khan
      use this formula i will make sure there formula is work.
      =DATE(YEAR(D14)-C14,MONTH(D14),DAY(D14))
      D14- mention Year Cell Range
      C14- mention Date Cell range

  27. I have a doozy for you. based upon the date entered into column A, I want a formula to determine if that date is on or before the 15th of that month. If the date falls within that range then I want it to return a value for that month and year in column B. If the date in column A is after the 15th of that month then I want it to return a value in column B that is for the next month and year. For example: If someone entered 6/2/2017 in column A, then column B would return a value of 6/1/2017. If someone entered 6/16/2017 in column A then column B would return a value of 7/1/2017 in column B.

    Thanks so much. Great forum!

    1. Hello,

      Please try the following formula:

      =TEXT(IF(DAY(A1)>15,DATE(YEAR(A1),MONTH(A1)+1,1),DATE(YEAR(A1),MONTH(A1),1)),"mm/dd/yyyy")

      Hope it will help you.

  28. The date imported displays mmmyy (Jan18) for Jan 2018. I need that to display 01/31/2018. How do I do that?

    Thank you in advance!

    1. are you trying to show the 31st / last day of the month as the output? For example do you want to see: 1/31/2017, 2/28/2017, 3/31/2017...?

  29. ANX171022

    what formula i should use to get the manufacturing date and expiry date from the above number

    ANX=Product name

    Manufacturing expiry
    17=2017 2020 (3 years from mfg)
    10=Oct oct
    22=Date NOT NECESSARY

    1. Hello,

      If I understand your task correctly, you need 2 different:

      For manufacturing date:
      =TEXT(DATE("20"&MID(A1,LEN(A1)-5,2), MID(A1,LEN(A1)-3,2),MID(A1,LEN(A1)-1,2)),"yyyy Mmm")

      For expiry date:
      =TEXT(DATE("20"&(MID(A1,LEN(A1)-5,2)+3), MID(A1,LEN(A1)-3,2),MID(A1,LEN(A1)-1,2)),"yyyy Mmm")

      Hope this will help you!

  30. please give the solution that subtract the date from 05-12-2016 to 06-04-2016, but the result should be 275 days (30 of April+31 of May + 30 of June +31 of July + 31 of Aug + 30 of Sep + 31 of Oct + 30 of Nov +31 of Dec), which is received from the end date of Dec 016 to first April 2016 (ie) 31-12-2016 (-) 01-04-2016

    Please explain with support of EXCEL Function

    S.Arivananthan

    1. Hello,

      Please try the following formula:

      =DATEDIF(EOMONTH("06-04-2016",-1),EOMONTH("05-12-2016",0),"D")

      Hope it will help you.

  31. Hi:

    Let's say that I have columns A and B. I need to insert a date in column A and automatically appears a date but 90 days later in column B. How can I do this?

    Thank you.

    1. Hello Camilo
      use this Formula your worksheet.
      =TEXT(SUM(A1+60),"DD-MMM-YY HH:MM")
      A1- Columns A
      Formula use - Columns B
      i think that we works.

  32. I need to use formula to calculate expiry date from age using following condition. If age is less than 40yrs, expiry date should be 5 years from examination date. If age is equal to or more than 40yrs, expiry date should be 2 years from examination date. Please help. "D" TO BE DONE USING FORMULA.

    A B C D
    1 Age Examination Date Expiry Date
    2 57 7/25/2017 7/24/2019
    3 39 7/25/2017 7/24/2022
    4 40 7/25/2017 7/24/2019

    1. Hello, Sara,

      in addition to the DATE function (to add years) you need to use IF function as well. You will need to built a nested IF in order to go through all your conditions. So the formula should start like this:
      =IF(B2<40,DATE(YEAR(C2)+5,MONTH(C2),DAY(C2)),IF...

      And your nested IF continues :)
      Please follow the links from my reply to learn how the functions work and to insert the rest of your conditions properly.

  33. I need some help data 1 is same name but different output date.but data 2 is Unique name,so i need for which date in higher date only we have represent the output date.

    Data 1
    hai 4/20/2016
    hai 4/22/2016
    hai 4/21/2016

    Data 2 output
    hai 4/22/2016

    1. Change date Format
      20 April 2019
      22 April 2019
      21 April 2019
      22 April 2019

      And use this formula =LARGE(B5:B8,1)
      B5:B8 - Select Cell range area

  34. Hi Team,

    Your above methods are very helpful.
    I wanted to know how i can get the date of next month.
    For eg-: if i input 23-01-2017 i should get the output 22-02-2017

    1. Hi,

      Thank you for your question.

      Enter your date in any cell (A1 in my example) and try the following formula:

      =A1+DATE(0,1,0)

      As the logic of the DATE function is the following: DATE(year, month, day), the formula above adds a month to the date in A1. You can change the number of days/mounts/years as needed.

      Please let us know if you have any other questions.

  35. hello, what the formula of show month period like 01 January 2017 to 31 January 2017

    1. =text(a1,"DD MMMM YYYY")
      A1- Range(which cell selected) apply formula

  36. Can I update the cell references in a formula (i.e. add the next months value into the formula) by changing the month in a drop down list? I am looking to extend the formula each month and would like to accomplish this without manually changing every line.

    1. Hello, Sean,

      yes, Excel formulas recognize the values chosen from the drop-down list and return the values accordingly. Make sure that you reference the cell with a drop-down list correctly in your formula and it should work.

  37. Hey!!i am working on an hotel management excel where i am unable to calculate or either put date meaning i am not able to adjust check in time and check out time..Pls Help

    1. Dear Sir
      very simple formulas

  38. Hi there

    Wondering if you could help me with this!?
    Having a xcel file and im trying to get xcel to give me the proper dates depending on a bar code and the no. of days which the product has! So lets say i have:
    C4(bar code), H4(life shelf 12 days),I4(day which should be)

  39. Hello,i please need assistance.We award employees at 5 year intervals.Please help with formula to use.

    1. Hello, Spinky,
      If the starting date is in A1, then you place the following formula in A2:
      =DATE(YEAR(A2) + 5, MONTH(A2), DAY(A2))
      Check the link to see DATE function in more details.

  40. Hi,

    I got a calculation to do in excel and I am completely stuck. I need to calculate if a staff is eligible for local leave or not. The staff is entitled local leave after 1 year (365days). So, if I have a date of entry 9/04/2017 in the next cell it should say YES if not 'NE' Not Eligible.

    Can someone help me on this please?

    Br,
    John

    1. Hello, John,
      assuming that the dates are in A row, you need to calculate the following: date of hiring (A1) – date of entry (A2) = result (A3).
      Try the next formula in A3:
      =IF((A2-A1)>=365, "YES", "NE")

  41. I figured out how to calculate dates based off of a start date, however, when that initial cell is blank, it fills in random dates that I don't want to be visible. Is there a formula that I can embed into the cell so that they stay blank till the initial cell is filled?

    1. Hi, Johanna,
      if the initial cell is A1, try the following:
      =IF(ISBLANK(A1),"",FORMULA)
      Note, that instead of the word FORMULA you put that formula of yours to calculate the date.

  42. Hi, I am trying to create a formula that will return a result if the current month is December or if the date range is between Dec 1 - Dec 31 (not specific to year), otherwise return 0.

    I have this so far... but it is dependent on the year being there and being updated every year. How can I make it so that it is any December ?

    =IF(AND(TODAY()>=DATE(2017,12,1),TODAY()<=DATE(2017,12,31)),'DR Schedule B'!F31,"0")

    1. I have found one that works!!! Thanks

  43. i have a problem in excel that is when i am entering 1-1, 1-2, 1-3 ,....... 1-11,1-12 it is converting into 01-jan, 01-feb,...... 01-nov, 01-dec . How to convert them into numbers when i am entering 1-13 it stay in 1-13

    plsss give a detailed solution for my problem

    1. To get the number as you required is to add ' before entering number.
      put '1-1, '1-2 and if you drag you will see

  44. I am trying to have a cell show a date 30 days less than the date in another cell. So cell A1 has 03/31/17. I want cell A2 to calculate A1 minus 30 days. Please help!

    1. Never mind. When I actually tried in Excel, super easy. I am trying to find an IF formula that I can use in Smartsheet to get this done. The simple =A1-30 that works in Excel is not working in Smartsheet.

  45. Sorry I can't find a solution to what I'm looking for in the large amounts of questions. Any help greatly appreciated:

    I'd like a formula that adds a certain value, or indeed a multiplier, based on a date of the month. i.e a cell that adds another €100 automatically every 25th of the month, even better if it can stop after a year (Jan-Dec)

    I'm aware there would be the very long IF function variant using TODAY and a bit of juggling, but there must be a neater formula?

    Would love to hear some ideas. Thanks!

    1. A botched workaround to my above question:

      =IF(TODAY()<DATE(2016,12,31),(ROUND(((TODAY()-DATE(2016,1,1))/30),0)*[desired value]),(12*[desired value]))

      Does the trick, just only comes into effect half way through the month and assumes 30 days in each month.

      Would love to hear how to nail it down to a specific date.

  46. Good day,
    Can you maybe help. I have a spreadsheet with the age of issues (issue log). I need to split the ageing of the issues into 60 days to count how many of the issues in each of the ageing falls to present it in a pie chart at the end.

    How do I calculate the counts for each ageing categories?

    Thank you,
    Leana

    1. Sorry, I noticed the detail is incorrect - apologies.

      It must be split between 60 days.

      1. Sorry, it seems like it converts my calculation....

        It reads: It must be split between less than 30 days, 31-60 days and more than 60 days

        1. Hi Leana,

          Please provide more information on how your data is organized. In particular, how should the aging of issues be calculated? Do you have a column of issue dates that should be compared with today's date?

  47. Can you help me out with following problem.
    I want to calculate total delay time and the total early time in the attendence sheet.as a example,our office start at 7:00AM. if some one came at after that time or early that time,I want to calculate total delay times and early times for a month.

    1. please help me

  48. If I don't have "B" then I want my result to be a "0".
    How can I do it???

    Example:
    If I want to get the days since something is open and one of the dates is empty.

    "A" Date of report (I will always have this information)
    "B" Date the action is open (I will not have always this information)

    my formula is

    ="A"-"B" = days since it has been open.

    If I don't have "B" then I want my result to be a "0".
    How can I do it???

    1. I found the answer :-)

      =IF(B="", 0, A-B)

  49. I have two dates in two separate column and I want to have which ever date comes due first to populate into a new column. How do i formulate that?

    1. Hi Ryan,

      Assuming you are comparing dates in columns A and B, the following formula should work a treat:
      =IF(A2

  50. For the past year I have been using a formula to keep track of when my patients are due for their next visit. For example, if I visited today and they are due again in 4 weeks, I would plug in today's date and the formula gave me a date 28 days from today. The formula I was using was =SUM(column and row of today's date,number of days to next visit). So it might look something like this: =SUM(C3,28). That was working well up until a few weeks ago when all of a sudden, the projected date was coming up as a series of pound signs (#########). Has something changed with Excel?

    1. Yes, the column is to narrow

    2. Never mind. My future date column was too narrow for double digit months. When I widened it, the pound signs turned into the date. :-S

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)