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

  1. Hi,
    I am trying to create a spreadsheet that alerts me to the approaching time limit of some applications.
    I need a formula that will use the "valid date" in column A and "decision date" in column E to tell me via colour code:

    * Red - 0-7 days from valid date to decision date
    * Amber - 8-24 days from valid date to decision date
    * Green - 25-100 days from valid date to decision date

    I've tried a few variations of things with no luck and it's driving me insane!!

    Thanks in advance

  2. Hey!
    Need some help. I created some sort of Date Picker from 3 cells (A1- dates 1-31, A2- months 1-12, and A3- years) dropdowns. Using Formula in a different cell =IFERROR(DATE(A1,A2,A3);"Not a proper date")
    Formula works as such, but if I pick from dropdowns 30.feb.2019, the formula returns 01.mar.2019...
    How can I fix this so i get in return that the date is not correct?

  3. how could I get remaining months? (2 years (Length of Lease) minus a date (start of Lease))?

  4. pls, I want to do conditional formatting whereby the cell is highlighted yellow from 10 days before maturity till the day its matures. on the day of maturity, I want the cell to turn green.

  5. HELP! I want to know a formula,
    Lets say if the person join the company before year 1993, he will retire at year 55years old AND IF the person join the company in 1993 and above he will retire at year 60years old.
    D12 = date of birth
    G12 = date joining the company
    and if its a woman, she will retire at 60years old no matter what
    HELP

  6. I want to use formula for date sorting
    example: Item A have a 3 delivery date like Row 1 : 10/02/2019 Row 2: 18/03/2019 Row 3: 05/01/2019 Row 4: 20/05/2019
    Than date serial number require using formula like:
    Row 3: Serial number 01
    Row 1 Serial number 02
    Row 2 Serial number 03
    Row 4 Serial number 04

    Please suggest which formula use for the same.

  7. If I use the edate formula for 06/30/2019, and then want to go back 1 month it returns a value of 5/30/2019, however I there are 31 days in May, I need the formula to figure out what month I am subtracting and caculate it correctly so that I end up with the actual end of the month day of 5/31/2019.
    =EDATE(E97,B97) where E97 is 06/30/2019 and B97 is -1

  8. I wonder if anyone can help?
    I need to work out the date that a valuation total went over 90% of the total Contract value?
    i.e. column A would be the valuation date and column Z is a formula with the total contract % so far, I need to work out the date when if first goes over, any way of doing this

    Thanks
    Kelly

  9. I have a column titled 'Review month' and a column titled 'Requires Review', the 'Review month' column is just text (e.g May). I am trying to create a formula so that if 'Requires Review' is within a month of 'Review month' it automatically fills the cell Red and says yes. if it is not within a month then it is to fill the cell Green and say no.
    Is it possible to create a formula for this or conditionally format the cell to do this?
    Any further information needed then please ask.

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

  11. Please help me with the excel formula. I am intending to mark first and second in a cell based on series of date of birth available in a different column by using a cut off date.

  12. Dear Team,

    I need formula where supposed I have dates from 1st May to 3oth May there have 100 counts.
    Now I need formula where from date 1st May till 15th May= month should be May only and there days which is more then 16th till 30th May its come in next month which is june

    Please help me

  13. Not seeing it in this thread so I will ask here.

    We have an event every third sunday of each month. Need a formula that can depict that and return a date.

  14. Please refer to my problem as i want to a calculation sheet like 31/03/2014 to 15/05/2014 then the result come Mar 01 days April 30 days and May 15 days. please reply

  15. I need to convert 13Y 11M into a date using today as the reference.

  16. I am Calculating the Year and Month completed between two date
    Start Date = 06-Jul-2009 and End Date = 31-Mar-2019

    Difference of Both Date is 9 Year & 8 Months, But i want a formula to calculate easily

  17. Please help me , i need a formula in my report to do it fast. The report is like this:
    i have to calculate days taken to pay the customers, but holidays and weekends are not included. These days are calculated from the day invoice is received untill it is paid.

  18. I would like to do a report to check if the customer paid on time:
    1. This is acceptable:
    (a) Due Date Due Date Due Date > 7 days

  19. Hi,
    Can you help for below query
    Payment Date DUE DATE
    03-04-2018 07-05-2018
    02-05-2018 07-06-2018
    22-06-2018 07-07-2018
    25-07-2018 07-08-2018
    I want to get due date by formula. Can you tell me by which formula I can get the due
    date as per above.
    Thanks,
    Ashish Sharma

  20. Hi
    Can u pl help me how to remove slashes by using formula. Ex: 12/31/2018 (MM/DD/YYYY) to 12312018.
    Thank u in advance.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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