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 9. Total comments: 684

  1. I have 3 date one is TDR Raised then TDR Closed Target Date & Last TDR CLOSED DATE.
    I want to calculate Lead time between TDR Closed Target date with the current date tha I have done, but after that I need to stop calculating the lead time after the TDR Closed Date.
    Please suggest

  2. Please help me, i need a formula that compute number of days of a certain years
    that in first 2 years is equivalent to 20 days deduction
    3 to 5 years is equivalent to 23 days deduction
    6 to 10 years is equivalent to 25 days deduction
    11 and above is equivalent to 30 days deduction
    example:
    Start date: March 23, 2017 end date: April 10, 2019 = 2 years 0 months 17 days total of 480 days

    *** Hoping for you support and Help.. God Bless

  3. how to calculate overdue amount with compare between two dates same. example. today 05.04.19.if due date same that then overdue amount current due otherwise current due plus other emi.

  4. Hello, I need some help here
    I have lots of sales data that I have to sort like this - Earlier this quarterly, Last week, This week, Later month, later this quarter, etc. All dates are in mm/dd/yyyyso i would like define them with text like This week, Later month and so on.
    There are booking dates and actual dates - So another condition to be added if there is an actual date then use actual date.

    Is there any logical formula i can use?

  5. Dear
    06-04-1998 convert in text Six April Nineteen Hundred Ninety Eight
    kindly help me

  6. I am involved in sports analytics. I am attempting to create a formula that searches back a certain number of days and sums the total within that number of days to come up with a stat similar to "goals scored in the last 5 games" or "Goals scored in the last 20 days"
    something along those lines. I have been able to figure out how to search this with a static formuala (search back to a certain date). Have not been able to figure out how to have it search from TODAYS date and back 15 days. Any help would be appreciated.

  7. Hi ,
    In excel for employee details i need to validate the date,so that when the user is inputing any date it should in date format and no other format(string ,text or anything),If there is wrong date entered it should not be taken can you please help me on this.

    Thanks
    Bhargavi M

  8. =IF(B10="Today",(Today()),IF(B10="Yesterday",Today()-1)) I need a result while i select the dropdown text like "Today" to day date will be display on cell.please give a solution

  9. Hi, I want to calculate the following logic built into excel for calculation EMI for loan
    - A person can select one of the two EMI starting dates (either 10th or 15th)
    - Say If I disburse USD 100000 on 15th Feb 2019, and the borrower selects 10th as the EMI due date, the first EMI will be due on 10th April 2019 or if say he selects 15th as the EMI due date, then the first EMI will be due on 15th April 2019. The difference between 15th Feb 2019 to either 10Mar2019 or 15th March 2019 will be the pre EMI date and interest will be calculated accordingly.

    Can you help me build this logic in excel so that If I select the disbursement date and 10th or 15th as the EMI due date, the EMI due date gets calculated automatically

  10. I have inherited a file with a pivot table showing years 2016, 2017, 2018 and >12/31/2018. How do you change the >12/31/2018 to show as 2019 so that I can pick January and February?

  11. hi
    I am Add sheet A1 year B1 Month then auto update D1 first Friday date, E1 second Friday Date, F1 3rd Friday, G1 4th Friday H1 5th Friday (january not 5th Friday then H1 cell show blank or Nill.

    EX:
    A B C D E F G H
    2019 01 or Auto update Auto Update Auto Update Auto Update Auto Update
    January (04/01/2019) (11/01/2019) (18/01/2019) (25/01/2019) No date then Nil Or Blank

  12. Hi all,
    Can someone help me on how to add a month in advance in a cell when select a month from a drop down from different cell

  13. Hi,

    I have a date in column H9 (formatted as mm/dd/yyy) I also have a date in column K9 (formatted mm/dd/yyyy) secondary format is H9+7 to return a full week.

    I then have column A22 - A28 that I would like to return the "MONTH" from Column H9 and then in column B22 - B28 to return the days 1-7 from H9.

    Any ideas?

    Thanks - Jenny

  14. please help me out with excel formula that will generate serial number from household number of 7
    i.e.
    7 - 1
    7 - 2
    7 - 3
    7 - 4
    7 - 5
    7 - 6 and
    7 - 7
    How can i get the formula that will do the serial numbering from 1-7 as stated above

  15. today date () is constatnt 02/01/2019 cell no u1

    16/10/2019 is cell no u25

    21/10/2020 is cell no u27

    16/01/2020 is cell no R25

    our formula is IF((MONTH(U25)&YEAR(U25)<MONTH($U$1)&YEAR($U$1)),R25,"")

    actual result is empty

    BUT RESULT SHOWS 16/01/2020,

    so kindly help me

  16. Kindly assist me. I have dates in MMM YYYY format, that is Jan 2005, Aug 2005, Sep 2014, Jan 2015, Aug 2004, Aug 2014 not following consecutively in a column. How can I find out if an entry is skipped using an excel function?
    Please help.

  17. Hi all,

    I need a formula that gives me a "Y" or a "N" on whether a date is greater than 180 days.

    Thanks in advance!

  18. Hi All,

    I am facing one issue while calculating date from one cell to another

    I have cell B4 and date in that cell is 31/10/2018 06:29
    I am using date formula in cell D like =DATE(YEAR(B4),MONTH(B4),DAY(B4))

    but i am getting error.

    Please help me.

    Thanks in advance for help

  19. Hi All,

    I am facing one issue while calculating date from one cell to another

    I have cell B4 and date in that cell is 31/10/2018 06:29
    I am using date formula in cell D like =DATE(YEAR(B4),MONTH(B4),DAY(B4))

    but i am getting error.

    Please help me.

    Thanks in advance for help

  20. HI SIR,

    I HAVE DATE OF JOINING THE EMPLOYEES I NEED AFTER ONE YEAR COMPLETION THE AUTOMATICALLY IN NEXT COLUMN 15 DAYS el IS GENERATE.

    PLS. HELP FOR SUITABLE FORMULA

  21. hi all,

    in my work an extract old data from our system.
    but we face that the date is mentioned as day/month/year.
    so when i open this extracted file in office 365, excel read these cell as text and not as date number.
    so here i need a formula that convert these cells as month/day/year in order to can read it as date value in 365 excel.
    i.e: 20/4/2018 old version it means 20 April 2018.
    it must be 4/20/2018 for the new version.

  22. Hi,

    I am working on a spreadsheet where I have to update number of Fridays in the month based on the current date.Currently,I am using the formula COUNTIF($C$11:$C$41,">0")-0.167*0 where I update "0" manually depending on whether its the 1st Friday, 2nd etc. Can someone assist to do this manual input automatically. The ($C$11:$C$41,">0") counts the number of days that the plant produced.

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

  24. Hi,

    I want to be able to add an increase to a salary if someone was employed between 2 dates for example:-

    if someone joined after 1st March 2012 and before 1st Jan 2018,increase cell B5 by 1

    Thanks

  25. Hello sir,sir which formula used for all company employee experience in age between 0to5,5to10,20to30 in excel

  26. Hi, I want to highlight day 15 of every month between years 2019 - 2020 in a single row on a Gantt chart. Any suggestions about how to do this?

  27. I am using formula index
    i have two sheets, and in the sheet 1 for the data on transactions per each have the transactions and second sheet i am using the formula

    =IFERROR(INDEX(Table1[Column2],MATCH($C$2,Table1[Column1])),"No Data")

    and i am facing the problem on match $C$2, (this one i am using match with month) how i can write all date 1 to 31 (month)
    C2 i am using 31-01-2018 and result only the particular date, i need whole month data

  28. Hi , can someone help me with the following please ? I need to calculate the re-test date for students depends on their score ,
    e.g. If students score more than 80 marks he must retake his test within 3 years max, if score between 30-80 he must retake his test within 2 years , if score lower than 30 he will retake his test within 1 year ...
    The date of the test is in column B, Score in column C and I need to calculate the date of the re-test in column D .
    ( it's not that simple in real life but for this purpose I had to simplify so you guys understand :) )

    I would really appreciate any help whit what formulas to use . Thank you

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

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

  31. Hello There,

    You can use below formula:-
    DATE(YEAR(A1),MONTH(A1),DAY(A1)+30) for 30 days.

  32. Hii Concern,

    I just wanted a formula for example,
    ending date of one card is like 25/08/2018 and one more card for the same person expiry date is like 26/09/2018 and the output i need is like
    by adding +60 days for first card and by adding +30 days for second card i need output is which ever less date will comes that date i need as output,
    can someone pls suggest.

  33. Hi,
    I would like to know the formula to get the below requirement
    If i type 2018 it should provide the date in this format 1 Jan 2018 to 31 Dec 2018
    if i type 2017 the result should be 1 Jan 2017 to 31 Dec 2017

  34. Want to formula
    0-30 days 2%
    30-60 days 1 % and
    60-90 days 0 %

  35. I was wondering if you could help me with a calculation. I need to be able to calculate 14 hours out of every 24 hour period from dates and times entered in 2 cells. For example cell A1 would have 4/10 13:00, cell B1 would have 4/12 18:00. I know how to do the total hours calculation, but we only pay for 14 hours out of every 24 hours with a 2 hour and 6 hour grace period. I need a formula to do this. The total hours would be 53. So I would need cell D1 to = 31 and cell E to = 28 For D1 we would need the calculation to show 4/10 15:00 - 4/11 15:00 = 14 hours paid, 4/11 15:00 - 4/12 15:00 = 14 hours paid, 4/12 15:00 - 4/12 18:00 = 3 hours paid...for a total of 31. For E1 it would be 4/10 19:00 - 4/11 19:00 = 14 hours paid, 4/11 19:00 - 4/12 18:00 = 14 hours paid...for a total of 28. Can this be done or is it possible to create a macro that would do this?

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

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

  38. How to convert (hex) to (day,month,year).

    D11-M12-Y16 - time 23 :22 : 48 - hex 62 69 E7 C8

    D12-M12-Y16 - time 04 :35 : 16 - hex 62 6A C0 84

    D12-M12-Y16 - time 05 :26 : 40 - hex 62 6A CC 90

    D13-M12-Y16 - time 11 :46 : 22 - hex 62 6E B5 0E

    D13-M12-Y16 - time 11 :48 : 33 - hex 62 6E B5 91

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

  40. How to convert (hex) to (day,month,year)-D11-M12-Y16, 08:43:25.

    Hex (0X62 0X68 0X8A 0X2D).

    Here is the data format.

    T4 - encoding 1:

    1. word 3*(365*(Year-1986) + DayNumberInYear) + Hour/8
    2. word 3600*(Hour modulus 8) + 60*Min + Sec

    DayNumberInYear assigns 1 to January the first and increments
    the number by one each day, treating leap-years correctly.
    Unfortunately, this encoding assigns the same 1. word to December 31. in a
    leap-year and January 1. in the next year.

    T4 - encoding 2:

    1. word 3*(366*(Year-1994) + DayNumberInLeapYear) + Hour/8
    2. word 3600*(Hour modulus 8) + 60*Min + Sec + 32768

    DayNumberInLeapYear assigns 0 to January the first and
    increments the number by one each day, assuming all years to be
    leap-years.

  41. Here's an old Lotus 1-2-3 method that works great in Excel and will accomplish what you're looking to do:
    =DATEDIF(D2,E2,"y") &" years,"&DATEDIF(D2,E2,"ym") &" months," &DATEDIF(D2,E2,"md") &" days"
    Enter this formula into F2 and it will show the number of years, months and days between the start date and the end date. It looks like this: "3 years,8 months,28 days".
    If you need to sum the years, months and days you may need to enter them into separate columns. If so, just enter the DATEDIF(start,end,"Y") pieces in separate columns minus the "&"Years", etc. part. You'll have only the numbers that you can then sum.

  42. I am trying to caculate the years a person has worked for company to a particuler date. Example is April 3, 2014 to December 31, 2017. D2 is the hire column and E2 is the ending date. I am needing to calculate the yrs, months, days worked in to column F2. I will also need to tally up the whole column of F2 for a grand total of the employees.

  43. I have a table with hundreds of invoices. some are paid in 30 45 or 60 days.

    I am trying to calculate the expected date that the invoice will be paid.

    amount is in b3 payment days is in c3 and I have next 75 days in columns f3 through the next ten columns.

    How do I write the formula to have the amount drop into the correct week?

    thanks in advance.

  44. Thank you in advance for your help!
    I am trying to do a summary sheet using cell values from sheet 2. We are calculating number of dogs or cats (2 different columns on the summary sheet so cats will be a separate formula - but they are all mixed together on sheet 2). But I want totals for the month so made that another condition. So 2 IF conditions - column F should equal dog and column A should be between two dates inclusive. I think I have the formula close here but it doesn't like the formatting of my dates. I have tried a few different ways - DATEVALUE, d/m/y, no spaces, quotations. Can you help? Thank you so much!!!

    =COUNTIFS(AND(Sheet2!F2:F1000=Dog, Sheet2!A2:A1000>=DATE(2018, 1, 1),Sheet2!A2:A1000<=DATE(2018, 1, 31)))

  45. Kindly reply with a solution to:
    (1) I creat a workbook for each month (30 worksheets) and have to enter date manually one by one at left hand corner to each worksheet e.g. 01-Nov-2018; then next work sheet will be 02-Nov-2018. Is there a way I can enter the formula in the first worksheet and it will give me date in sequence on the rest of the month worksheets?

    (2) The Tabs (name) for each day worksheet also need to be changed manually e.g. 01 Nov AM, 01 Nov PM, 02 Nov AM, 02 Nov PM ...

    Thank you

  46. I'm trying to calculate dates in excel. Could someone help me with the solution for this?
    I want the single formula to be calculate automatically. Do we have any formula for this?
    Example: Today’s Date is 25 mar 2018
    Sl. Order Date Sent date Overdue Days
    1 22 Mar 2018 25 Mar 2018 3
    2 22 Mar 2018 Not Yet Sent 3
    3 20 Mar 2018 25 Mar 2018 5
    4 10 Mar 2018 Not Yet Sent 15

    Thank you in advance

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

  48. Is it possible to know the difference between 2 dates if the date format is January 31 2018?

    If yes, can help on the formula?

  49. Hey guys,

    I'm trying to calculate range in excel. Could someone help me with the solution for this.

    I want the range to be calculated automatically. Do we have any formula for this?
    example:

    Sl. Employee Years Range
    1 Abc 5 5 - 6
    2 DEF 8 8 - 9
    3 GHI 5 5 - 6
    4 JKL 5 5 - 6

  50. I am trying to get the days of the week in column A for the whole year but skip Saturdays and S, is there a formula for this?

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