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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  25. how to date format change
    ex: 01.01.2017

    i need 01-Jan-17 format

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

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

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

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

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

  31. hi, pls i will like you to help me with the steps on how to convert text into numerical values Using Excel 2010.

  32. Hello,

    I am attempting to calculate the inventory value based on the inventory quantity (in column B) and today's unit price (columns C through I represent the days of the week and have unit prices below each day for each item; I have a new sheet for each week). I want the inventory value to display in column K following each type of item in my inventory (column A for each item).

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

  34. Hello please help. What is the calculation for this. I want to get the average and/or sum.

    ColumnA: 15 days, 1 hours
    ColumnB: 15 days, 6 hours

    Thank you

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

  36. hi, can you help me with some date formula, i wanted to put formula in cell A1 where when you write anything on cell B1, the cell A1 will automatically generates a date for that day.... and on the next day, when you write on cell B2 the the cell A2 will automatically generate a date on the same day but will not affect the cell A1... meaning if today is January 1, 1900 and write anything on cell B1, cell A1 will be dated January 1, 1900 and when i write tomorrow on cell B2, cell A2 will be dated January 2, 1900 but cell A1 will still be January 1, 1900.... hope you can help me.

  37. Hello,
    I have a table with formulas containing dates in them. Is there a way that these cell formulas automatically update with new results every time I put in new weekly dates in a separate column?
    (Example: Columns S6 till S12 - Sunday, 3/26 till Saturday 4/01)

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

  39. Hi
    i'm looking for a formula to get service years on a specific date. For example, joining date 01/01/2015 in cell A1, what is the formula to get years on 31/12/2016 ?

  40. Hello Everyone

    I've been looking everywhere for help to my edate error but cant find anything that works. Hope someone here can help.

    I am trying to use edate to subtract 12 months from a given date to use in a look up calculation for Fiscal YTD results. Everything works fine until February 2017 rolled around and the results give me February 28, 2016 not February 29, 2016. Which causes a #N/A error.

    =IF(B8=EDATE(Q3,-12),B10,SUM(B10:INDEX(B10:Y10,1,MATCH(EDATE(Q3,-12),B8:Y8,0))))

    Is the formula I am using which works fine for every other month.

    Q3 refers to the current month end date: in this case: 2/28/2017

    Row 8 has dates in subsequent cells as follows:
    7/31/15 8/31/15 9/30/15 10/31/15 11/30/15 12/31/15 1/31/16 2/29/16 3/30/16 etc all the way up to my 2/28/17 month.

    the formula returns: 2/28/16 not the 2/29/16 I need to get my prior fiscal year to date total.

    In other words my formula returns the date as 42428 not 42429. Which the causes the formula to error out since there is no 42428 in the date cells.

    Thanks in advance for the help.

  41. HELLO DEAR
    I WANT SHOWN DATE IN EXECL IN BELOW PATTERN
    12/05/2016
    I WRITE 120516 AND ITS SHOW AS 12/05/2016

  42. what formula can I use to calculate the minimum $ value in a colum from todays date forward for every time I open my spread sheet.

  43. You guys are great! looks like you've helped a lot of people. Here is another one for you.

    Im trying to format columns based on an start date and an end date. I've been able to insert a start date and get the following months to populate by using =EOMONTH($A$7,C$1) A7 being start date and row one being by number of months following. As it is now i have add or subtract columns manually to get to the correct end date. Then if i change the beginning date all the cells formatted below are in the wrong months.

    If you can it'd be great to get some help thanks.

  44. I just found your blog and am amazed at the detail and depth of your responses! Quite impressive! I want to create a macro for entering payment data into certain rows of column M (where each row is one day) based on options from a drop down menu (accessed when any cell in column M is right clicked) and based on the date in column A. The drop down menu options are: amount, start date, frequency (1 week, 2 week, 4 week, monthly), and number of payments (max 500). If the entry falls on a weekend, it should be moved back to fall on the Friday before. The first entry will be on the same row as the row of the date in column A and the remaining entries similarly. I know this is fairly complex and thank you so much for your help!

  45. Hi, i am property broker and i want to yellow colour in after 330 days and Red colour after 360 days formula reminder. plz help.

  46. I have a cell downloaded from a web report that contains 13/02/2017 15:30:00 all in one cell.
    How can I extract the date, and then the time into two separate cells further along the page?

  47. Hi, I am trying to use this formula to calculate date difference but I also want the formula to return 0 or do nothing for lines without dates.
    Formula used is =DATEDIF(Q5, TODAY(), "d")/7
    The problem is once I drag down it also fills the lines with blank dates as 6111 which is incorrect but lines with dates are correct.

  48. How do you calculate the number of days from start to end and adding 1 to the results?
    Start : 6/4/2018
    End : 6/6/2018

  49. Enter a formula in cell G5 that calculates the difference between the attendance totals for 2018 and 2017. Copy the formula to the range G6:G11.

  50. Hello,

    I need a date formula for example:

    The two dates are set at 09/01/2016 -10/01/2016 calculating the daily rate at £44.02 but I need it to acknowledge the two dates as two days not one and return the daily rate as £88.04

    Please help...

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