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

  1. Doc Due Mode Premium Recevable Receved Recevable Receved
    1/20/2020 5/20/2020 M 2000 5 4 10000 8000
    1/20/2020 7/20/2020 Q 5000 3 2 15000 10000
    1/20/2020 7/20/2020 H 10000 2 1 20000 10000
    1/20/2020 1/20/2021 Y 200000 2 1 400000 200000

    Plese can you send me What is the Excel formula (Received /recevable) ammount

  2. Hello
    Sir,
    Please tell us formula date to 09/04/2020 dd/mm/yyyy

  3. Buna ziua

    Tot incerc sa aranjez un tabel in ordinea cronologica a datei calendaristice si nu reusesc.
    Cum pot sa rezolv aceasta problema?
    Am incercat multe variante si degeaba.
    Cu multumiri

  4. I want to update 1 tracker, how can come todays date, if i'll open file before 12.00 AM, if any reason, i'll not able to open file before 12.00 AM, I want to previous date.

  5. Hi, I need to define/include any real month, as to the ( from last day of previous month to one day before the last date of current month). Can i see a calculation for this?

    Ex: Real month - (November 1st to 30th November)
    My definition November - (31st October to 29th November)

    Wayne C

  6. Thank you, Thank you, Thank you ... that is exactly what I wanted it to do

  7. Good day
    I need a formula to help me with Invoicing payment dates. So if I receive an invoice on the 20th or before that, the invoice needs to be paid end of the following month (eg Invoice date - 20-Nov-20 and payment will then be 31-Dec-20), but if an invoice is received after the 20th of a month, it needs to be paid 2 months later (eg Invoice date - 22-Nov-20 and payment will then be 31-Jan-21)

    Thank you in advance

  8. I want to create cells that adds a range of days to dates with parameters: not to be less than the first day of the next month and not to exceed the last day of the next month. I enter a date in one cell, next cell adds 28 days but at least first day of next month, third cell adds 35 days not to exceed the last day of the next month.

    1. Hello!
      Formulas that calculate the first and last day of the next month

      =EDATE(D3,1)-DAY(D3)+1

      =EOMONTH(D3,1)

      I hope it’ll be helpful.

  9. Hi
    I need to set a formula in excel so when one cell is updated with a month and year (e.g. Jan 20) the cell below it automatically updates to 3 months later (e.g. April 20). Does anyone know how to do this please?

  10. please i need your help
    i have excell work sheet from 1st september to 30th with for formua , now i want creat new sheet for 1st october such that the 30th september minus (-) 1st octomber gives zero

    1. Hello!
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Perhaps you just need to subtract dates and subtract 1 more day?

  11. i have this formula =IF(O5"",O5-B5,Q2-B5) and when i drag it down it will be like this =IF(O6"",O6-B6,Q3-B6) how can keep the Q2 when dragging

  12. I need formula to calculate the aging for case created from the date of creation to the present date. which should be auto calculate on daily basis.

  13. =IF(AND(TEXT(B3,"HH:MM")="09:30")=TRUE,B3,IF(TEXT(B3,"HH:MM")>="15:40",DATE(YEAR(B3),MONTH(B3),DAY(B3)+1)+"09:30",DATE(YEAR(B3),MONTH(B3),DAY(B3))+"09:30"))

    here i have formula where date & time convert after (15:40- 23.59) to next day morning 09:30 am
    e. =if B3 date and time is 08/01/2020 15:50 it will convert to 08/02/2020 09:30 am
    simultinioulsy aft 00:00 to Morning 09:29 am it convert same date 09:30 am
    eg.if B3 date and time is 08/02/2020 01:00 am it will convert to 08/02/2020 09:30 am

    but as on on 2nd aug was sunday so i need to converet the same date AND TIME WITH 08/03/2020 09:30 AM

    PLS HELP TO GET IT DONE
    VINOD J

  14. Hi,
    I want to make a spreadsheet of cut off dates which will keep changing every year as the sheet is opened. How will I get this? Whats the excel formula?

  15. I have a spreadsheet with completion dates. For Example:
    2/21/19
    5/24/20
    11/28/19
    I need to calculate the percentage of how many I have that are less than 365 days. How would I go about doing this?

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

      =YEARFRAC(DATE(YEAR(A1),1,1),A1)

      I hope my advice will help you solve your task.

  16. Hi. I am trying to calculate future dates from today using a given date in the past such as given date = 6/1/20, today 7/6/20, the fixed day of month (excluding weekends and holidays) is the 1st of each month so I need my formula to tell me the next date from 7/6/20 (in this situation would be 9/1 then 10/1, etc) and to continue for a given period of time. I have the following formula: =WORKDAY(EDATE($B$6,2)-1,1,Sheet2!B1:B14) but it will give me the next month based on todays date. Help please

    1. Hello Angela!
      If I understand your problem correctly, you want to determine the first working day of each next month. The formula below will do the trick for you:

      =WORKDAY.INTL(EOMONTH(A1,0),1,1)

      I hope this will help

  17. I need assistance please. I am working on a spreadsheet with a Surname in Column D; Admission date I; Discharge date K; Date back at work L; Deceased Date M; Healthy/Deceased N;
    My formula: =IF(D3="","",IF(N3="Deceased",(_xlfn.DAYS(M3,I3)),(IF(L3="",(_xlfn.DAYS(TODAY(),I3)),_xlfn.DAYS(L3,I3))))) So it means if there is a Surname in D, Look in M if deceased; if deceased calculate Deceased date, if not deceased calculate the Days off sick. What am I missing or doing wrong? I only get the #NAME? Back.

    1. Hello!
      I noticed the smart quotes in your formula and I suppose they are the cause of the error. Please convert your smart quotes into the straight quotation marks, that should fix the problem. Also, you may check this Microsoft manual to learn how to disable smart quotes: https://support.microsoft.com/en-us/office/smart-quotes-in-word-702fc92e-b723-4e3d-b2cc-71dedaf2f343
      Check the formula. I have this formula

      =IF(D3="","",IF(N3="Deceased",(DAYS(M3,I3)),(IF(L3="",(DAYS(TODAY(),I3)),DAYS(L3,I3)))))

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  18. contract start 01-10-2019
    contract end 31-08-2020
    how many days remaining for renewal

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

      =C2-TODAY()
      or
      =DATEDIF(TODAY(),C2,"d")
      C2 --- 31-08-2020

  19. Hi , i am working on a file where i want to get Today,Year,PrevExpiry, PrevExpiryStart, currentExpStart,CurrentExpiry,Expiry to calculate dates
    Whereas i am having Expiry dates for a year. Expiry end on Thursday of every month, so Next day,ie Friday will be Start day for expiry.
    The problem is working days are Five in the week and there is holidays and if expiry date is holiday then it will take previous day and if it Last day of the month then it should take first day of the next month.
    Which is not coming in my formula.
    =DATE(YEAR(Today),MONTH(Today)-1,0)+MOD(-WEEKDAY(DATE(YEAR(Today),MONTH(Today)+3,0),2)+1,-7)..where i am missing

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  38. Thank you for the info!

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

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

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

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

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

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

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

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

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

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

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

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

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