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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  31. ty so much for your amazing page and information.

    I am trying to create a spreadsheet for my sales tracking. If I ordered a specific document on xx/xx/xx date and have input that date into Column A2 for example(labled -date requested), I will input the received doc. date in column B2.
    But if no document is received. I would like for column B2 to turn green(when column A2 date is input(day1) and yellow when 1 day past the initial A2 date, and red when 2 days or more past the initial A2 date(request date) to inform us that the document has not been received. However, if a date has been inputted into B2 these rules(Red, yellow and green) will stop, because the date was added to B2 therefore implying that the document was received.

  32. I have column D for the date an item is issued. column E will calculate the date from column D plus 180 days. I would like to be able to input the current date into column F. And have it calculate into column G the amount of days between the current date in column G and the due date in column E. If you can help me with formulas I would be greatful

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

  34. Hi..

    I need an urgent help. I am maintaining a sheet where Supplier ID and Term start date is being recorded. Each supplier is appearing multiple times with a different term start date. I want to automatically put the respective term expiry date which will be a day before next term start date. Please help me with this.

    Thanks

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

  36. Suppose I want to have a column that has dates of completion of training and I want to have function where there are three colors, green is considered current, yellow is 60 days coming up on expiration and red has expired. How can I create this? Is it even possible? Thank you.

  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. Hello, i have a different value on Column A, and i have different date on column B. And I want to know the beginning & ending date for that particular value. What is formula for that? Thanks

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

  40. Hi there,

    I am using a Gantt Chart I made years ago and can't figure out why I created the formula the way I did.

    =(J5-WEEKDAY(J5,1)+M8)+7*M1

    J5 = is the Start Date (which is 1st of June, 2017)
    M8 = 1 (Day of the week which was Sunday)

    and then I have M1 whose value is 0 <-- this is the part that I needed to remember/understand, why I declared the value as 0

    Given that the Start Date is 1st of June, the actual value I get is the 28th of May

  41. Hi Daer All,

    Pls someone help in my problem

    I have a problem for calculating salary between two dates e,g Logic 1 Manual calculation & logic 2 is the based on the two dates. When calculated from logic 2 there is different between logic 1 amount 1,797. The case listed.

    Logic 1
    Manual Days calculation
    Start date End Date Salary Montly days Formula Prorata salary
    24-Mar-17 31-Mar-17 49,500 8 =E9/31*F9 12774
    Manual 12 Months calculation
    Start date End Date Salary Montly Months Formula Prorata salary
    1-Apr-17 31-Mar-18 49,500 12 =E14*F14 594000

    Total days+Montsh 606774

    Logic 2
    Start date End Date Months Salary Montly Formula Prorata salary
    24-Mar-17 31-Mar-18 12.22 49,500 =F2*E2 604,977
    Total 604,977
    Different Logic 1 Vs Logic 2 1,797

    Months calculation formula
    =(DATE(YEAR(C17),MONTH(C17),DAY(C17))-DATE(YEAR(B17),MONTH(B17),DAY(B17)))/(365.25/12)

    Note : Logic 1 is giving correct amount.

  42. Great info that you have here. Please help. I am working on my sheet of manage data,I want excel to series easily for me
    kindly help me
    how can i put the series in this way
    for example
    01/2017
    02/2017
    03/2017
    main thing is that 01, 02, and 03 is not date or month
    this is my work sheet serial number i have alot of work sheet thats way i m manage one sheet for inform me to whos my work done or not
    so kindly help me how can put serial
    01/2017
    02/2017
    03/2017
    04/2017
    05/2017
    continues....

  43. Hi
    I want to count a Today date in a particular cell,Suppose my all data in sheet1 and and i prepare a formula sheet on sheet2,in that i want a count from a cell Today date only(as Like so many date are there in that i want to count how many thing dispatch on Today date),so that directly reflect to my sheet2.

  44. Hi there :)

    What is the best excel formula to use, to calculate the actual date based on the xx number of days + a specific date, where its counted as day 1?

    The formula I'm using currently, worked correctly on some dates, but not on others, it's one day less. Could this be due to the Stat holidays within the range?

    For example,
    a. 2 days starting from Aug 5, 17 = Aug 6, 2017

    b. 3 days starting from Aug 5, 17 = should be Aug 7, but the same formula I used for "a" returned Aug 6

    c. 7 days starting from Aug 5, 17 = Aug 11, 17, but returned Aug 10.

    Please help! What am I missing....

  45. how to date format change
    ex: 01.01.2017

    i need 01-Jan-17 format

  46. Hello :)
    Please let me know, How can I segregate the COLORED dates in to different columns.
    Recently i did it manually but its very time taking procedure, please guide me to find an easy way.

  47. Hi, I have written a macro to process dates as the X axis and the value as the Y axis. My challenges are that the length of column of dates could vary so some cells in the plotting range of the Macro could be empty (this returns default date in that cell of 01/01/1900. So my first question is how to use the VBA code so that the range is only taken with all cells containing a date and blank cells are ignored?

    Next the macro will format the graph but i cant find a way to arrive at the X Axis minimum and Maximum Scale limits, if i use the "auto" function then the graph does not display until I reduce the Min and Max date number to either side of the actual date range. I thought i could use the DATEVALUE function to set the date value number as an integer and use that in the MIN and MAX graph formatting Axis code: "ActiveChart.Axes(xlCategory).MinimumScale = 42817" and "ActiveChart.Axes(xlCategory).MaximumScale = 42823". Replacing the number with the Integer for the start date and end date of my range.

    Any suggestions on the above, I have searched a lot but not found the answer yet.

  48. Trying to complete task that is completed by completion date with a total of percentage.

    10 task starting at B2-B11 and dates are entered to show completed how can I get B12 to show total percentage completed??

    A 2-12 has names of task

    any help is appreciated

  49. I want to know how to remove date formula to enter other date beyond a particular date example i want to entre 10-05-17 but formula is set for 07-05-17

  50. A former co-worker created a spreadsheet for our reimbursement use but the calculating/formula fields are locked down and I don't have the password. I need to recreate this spreadsheet and one of the fields/formulas has me stumped.

    Column A is a date column for manual entry by users. Starting at row 9 users enter a single date (04/25/2017). Single dates can be entered up to row 54.

    The formula I need is for a period of time, based on the starting and ending dates found in column A.

    This is what the locked field looks like with no data in column A: "From 1/0/00 to 1/0/00"

    This is what the locked field looks like with data in column A, row 9 (1st row of data) & row 12 (last row with data): "From 04/24/2017 to 04/25/2017".

    My TEXT formula is not correct
    (=TEXT(A9,"mm/dd/yy")&" - "&TEXT(A54,"mm/dd/yy")

    The result is this:

    "04/20/2017 - 01/00/00" (even though I have data/dates in rows 10-17)

    I'm not sure how well I have explained myself but any help is greatly appreciated. Thanks!

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