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

  1. I am trying to make a chart with conditions that applys a date plus a number of days if a cell states yes in a new cell. I cant think of a proper formula. Can anyone help?

  2. need a formula where we have a due date and mail out date, the due date will determine if it's past due or current and the mail out date will determine if paid, under a status column, cannot figure out how to have 2 IF functions that helps with the 2 logical test that can give us these 3 statuses, paid, past due, current.

  3. Hello team,
    Need help... How to calculate the collection date when 57 days credit term starts from the end of each transaction week?
    Invoice date is April 1, 2020

    1. Hello!
      I’m sorry but your task is not entirely clear to me. What is "transaction week"? We are not experts in finance and credit. Therefore, we can not understand your terms.
      For me to be able to help you better, please describe your task in more detail. Please let me know in more detail what you were trying to find, what formula you used and what problem or error occurred. It’ll help me understand it better and find a solution for you. Thank you.

  4. I want to create remarks that denote that a particular activity is due in certain days (based on the last date when it was performed, mentioned in the sheet) or a particular activity has passed *this* number of days past its due date (also mentioned in the sheet)
    The sheet has current date, activity performed date and due date in columns. Kindly help

    1. Hello Swaleha!
      If I understand your task correctly, the following formula should work for you:

      =IF(B1="","Performed in "& (C1-A1) & " day", (A1-B1) & " days ago")

      If there is anything else I can help you with, please let me know.

  5. I'm trying to look at a priority level = A (+2 days), B (+5 days), C (+10 days) or D (+20 days) (in column E) then add the corresponding number days (+# days to column K) from the date in column C. Example in column C I have "01 Jan 20" and in column E I have "B" therefore I want column K to automatic insert "06 Jan 20" which is the +5 days due to priority level "B" selected. Thank you in advance for your assistance.

    1. Hello Ken,
      Please try the following formula:

      =C1+CHOOSE(MATCH(E1,{"A","B","C","D"},0),2,5,10,20)

      I hope it’ll be helpful.

  6. How can I from say cell N11 Up until I11 count all the days that art between as a total amount of days that have passed so far does my question make sense?

    I have a start date and a end date but as the days in between get added in as time goes on they need to be subtracted from the amount of days in my days *total column*.

    *Start* *End* *Total Days for project*
    03/03/2020 dates dates dates 05/05/2020 Days passed/left?

  7. Hello
    I am trying to create a table whereby I set one anticipated start date and this can then autofill other cells by putting in how many weeks after (eg. start date is 05.03.20 so if I put a 2 in the weeks column the date would show up as 19.03.20). Does anyone know if this is possible/how to do it?
    Many thanks
    Ella.

  8. I WOULD LIKE TO DRAFT AGEING REPORT USING EXCEL.
    WE PROVIDE LOANS, I WOULD MY EXCEL TO HIGHLIGHT A DAY BEFORE THE DUE DATE

  9. Hi
    i am trying to get a yes or no answer for:
    Employee's hire date is greater than 3-31-2019, yes or no
    but an employee hired on 1-2-1990 is showing as "no"
    please help

  10. I have a question.
    Im new to excel and my boss wants me to calculate Day,month and year from a different cell.
    and it's about retiredate. I have to calculate from their birthdate to the next 60 years. I mean I can use this formulas [=date(year(cell)+70+if(month(cell)>=10,1,0),9,30)] but just in case their birthdate is in the same cell. but from the different cell I can't. I really don't know what to do with this. I really appreciate if someone can help me.
    ***sorry for my english***

  11. Hi Can you help.
    I am trying to track absence and using =DATEDIF(D62,$N$2,"d") to give me the total of days someone if off sick, however I need it to stop counting when they return to work. Any ideas?
    Thanks,
    Steve

  12. I am trying to create a schedule in table format where the date format is ex (Sunday, Dec 02)and want it to autofill across the top. I also need this to be able search by day or date for an app.

  13. I am trying to create a formula to find the departure date from a loading date...for eg if loading date is 2nd December and departure for that specific country is Tuesday/ Friday then the departure date should be displayed as 3rd December. if loading date is 4th December then departure date should be 6th December Friday. I have 1 column with the country which is vlook'd up from another sheet in the workbook along with loading dates

  14. I wanted to create an Excel formula that produced an 18th birthday based on a date. Through trial and error, I came up with this one.
    Cell A1 = 4/26/2008
    Cell B1: =DATE(YEAR(A1)+DATEDIF(A1,TODAY(),"y")+(18-ROUNDDOWN(((TODAY()-A1)/365),0)),MONTH(A1),DAY(A1))
    Result = 4/26/2026

  15. separate 07/04/1959 to 07, 04, 1959 per column

  16. How would I separate birthday column of 07/06/48 to a separate column of month, day, year?
    Thank you.

  17. Hi
    I need a formula to calculate employees leave days "to date"

  18. Thank you for the info!

  19. Hello I am unable to change date format from MM/DD/YYYY to DD/MM/YYYY please help me

  20. My completion date should not chnage from previous date to current date. i want a fromula to track how many docs an executive has completed yday, today and so on..I have used now fromula as well but it chnages the date to present.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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