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

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

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

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

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

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

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

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

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

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

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

  11. Hello There,

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

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

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

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

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

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

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

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

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

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

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

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

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

  24. 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)))

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

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

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

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

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

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

  31. I have a question.

    I have a formula that pulls a date from another worksheet.
    =IF(Datasheet!M2"",TEXT(Datasheet!M2,"mmm-yy"),"Jan")
    In the cell adjacent to that "second date formula" I have a formula that creates the next month.
    =DATE(YEAR(Datasheet!$M$2),MONTH(Datasheet!$M$2)+1,1)

    Below each date I have a formula like this.
    =SUMPRODUCT(--(TEXT(Homeloans!$A$1:$L$1,"mmm-yy")=B$1),Homeloans!$A$2:$L$2)
    That pulls a value from the homloans worksheet.

    The problem is the "second date" formula will not allow me to pull the value from homeloans because of the way it is formatted.

    Any suggestions on how I should format the "second date formula" and it still create a dynamic month based on the first month in the series?

    Thanks

  32. Hi the below may be helpful to you.

    =IF(DAY(E8)15,EOMONTH(E8,0)+1,IF(DAY(E8)>15,EOMONTH(E8,0)+1,"")))

    Replace E8 with your active cell reference

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

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

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

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

  37. Looking for a way in excel to make cells that auto populate the fiscal week number. For example I put in column A3 "Week 45" the I want the next row underneath to auto populate to "Week 44".

  38. I'm trying to make a tracker, I need to cell the cells color when the typed in date 60 and 30 day (amber) and 30 to 00 days (red) from the one year mark. can anyone help me?

  39. How to increment the date for a week if we have particular condition as text using excel formula

    1. Hello, Spandana,
      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Please also don't forget to include the link to this comment into your email.
      I'll look into your task and try to help.

  40. >30 days from today column must show "0". how to make formula.

  41. Oh thank fuzzy kittens that I found you. If you can help it will be much appreciated. I need a column to show the fiscal year and week number. I actually need two formulas:

    Formula 1- calculate just the fiscal year, if the year starts Oct 1 and ends Sept. 30 (So if the date entered was Oct. 13, 2017 I'll get FY18 as a result). Next I need a formula that will

    Formula 2 - Calculate the week number if the year starts Oct. 1 and ends Sept. 30

    I have these two formulas, but they don't seem to work and I don't know how to fix them.

    FY Formula - =INT(([A1]-DATE(YEAR([A1]),1,10)+(TEXT(WEEKDAY(DATE(YEAR([Date of Engagement]),1,10)),"d")))/7)

    Week # Formula - =INT(([A1]-DATE(YEAR([A1]),1,10)+(TEXT(WEEKDAY(DATE(YEAR([A1]),1,10)),"d")))/7)

    If you can help me with this I would most appreciate it.

    Thanks!!

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

  43. Hi,

    I am working on next year's budget: Let's say an employees hire date is June 1, 2017 and they will be getting anywhere from a 0% - 3% merit increase on June 1, 2018.

    1 - I need to calculate the current salary amount from January 1, 2018 to June 1, 2018 at the current hourly rate ($10) by the number of hours worked per week (20) from January 1 - June 1.
    2 - I need to calculate the merit increase (3%) from June 1, 2018 to December 31, 2018 to get the new salary amount for that period.
    3 - I need to know what my total budget would be for the year and is it possible to get the total dollar amount in one cell?
    4 - Is it possible to create a formula to auto update this each year without having to manually change the year?

  44. Hi guys,

    Here's what i'm trying to get.

    I have a list of students with date of births, the rule to enter sports tournaments are the following

    U11 (Year 6/ Grade 5)– 1st Sept 2006
    U10 (Year 5/ Grade 4)– 1st Sept 2007
    U9 (Year 4/ Grade 3)– 1st Sept 2008
    U8 (Year 3/ Grade 2)– 1st Sept 2009
    U7 (Year 2/ Grade 1)– 1st Sept 2010
    U6 (Year 1/ KG)– 1st Sept 2011

    The date of births are set up this way 21-Dec-2008 (8.9) and i would like to have a formula which will indicate if a students is allegeable to play for the U11 or U10 ....

    thank you in advance

  45. I have hundreds of items with different project periods. Some have reports that have to be done monthly, quarterly, semiannually and annually. We roll the log of these items each month to track those that are currently due in a particular month. Does a formula exist that can calculate these due dates automatically??

    Today's Date 9/20/2017
    Month Ending 9/30/2017
    Project Period Report Due
    Project A 10/1/2016-9/30/2020 Q ?
    Project B 4/1/2017-3/31/2019 A ?
    Project C 9/30/2016-9/29/2018 SA ?
    Project D 7/1/2015-6/30/2018 M ?

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

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

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

  49. Journey start (12/5/2017. 6:00 AM )
    return time (16/5/2017. 9:00 AM )

    Return have total dinner, lunch, breakfast, and accommodation quantity.

    Condition:
    after 7:00PM he will not get dinner.
    after 1:00AM he will not get accommodation.
    after 8:00AM he will not get breakfast.
    after 12:00pm he will not get lunch

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

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