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

  1. Please someone help with my problem. i want to design a column that if its paid, the whole row will be green. if partially paid, yellow. is it doable? thank you in advance

  2. Hi, can you help me? I want to design a column that if the date encoded is today, the color will change into green. If it's past today, change to red. If it's within this week, yellow. Something like that. I hope someone can help.

  3. If in excel sheet coloum a mentioned as a feb 22,2021 so how we get in cloum b as a day name.

  4. Hello, hope you could help me, with this
    Target Date Date Submitted %
    January 25, 2022 February 2, 20221 ????

    How can I get their rate of their submission?
    Thanks

      1. Thank you for your reply,

        I am getting their performance in %
        They have their target dates to submit their data.
        Their Target is January 27, 2022 and If they submitted on February 17, how can i get their absolute performance. This is part of their performance evaluation review.

        Thanks

        1. Hi!
          You did not answer my question. If you explain how you understand "absolute performance", then I will try to help.

  5. Hi,

    I am trying to find out how to use a formula to find how many products have been purchased between two dates and then spilt them within products purchased (3 columns of products but i have not figured this part of the formula out yet).

    I've tried to use the following formula but it doesn't seem to work: -

    =COUNTIFS(E2:E88,">="&I94,F2:F88,"<="&I95)

    Please can you guide me.

    Kind regards

    Saiqa

    1. Hi!
      I’m sorry but your description doesn’t give me a complete understanding of your task. I can't guess what doesn't work in your formula. The formula is written correctly, but it is not known what data you have. I guess this article will help you: Count unique values with criteria
      If this is not what you wanted, please describe the problem in more detail.

  6. I am trying to create a formula that will allow me to determine when someone is expected to have something done by. Example. they start of Sept 10, 2021 and they must have a specific course completed within 60 days of the start date. Everyone one has a different state date and I must know how much time remains before the 60 days is up.

  7. Can you help me to convert this format 3.2021 (Month.Year) into 1-Mar-2021 please

    1. Hello!
      You need to convert text to date. The formula below will do the trick for you:

      =DATE(RIGHT(A1,4),LEFT(A1,SEARCH(".",A1)-1),1)

      Set the date format you need as described in this guide.

  8. Hi im trying the following, can you help.
    e.g ill write it phonetically
    if today = January the sum A1
    If today = february then sum A1 +b1

    its for adding up monthly targets

  9. I have the formula set as =DATE(YEAR(H2)+M2,MONTH(H2),DAY(H2)) and it is working in most instances where the date is the number in M2 is the number of years to add (i.e. the same date in 6 years time) .

    I need a way to modify the formula so that the date shows a different date instead.
    Basically, if the date in H is 07/08/2021 and the number in M is 6 AND it is an audit-able entry then I need the date formula to calculate our audit date plus 6 years which is 31 March (i.e. 31/03/2028 instead of 07/08/2027.)

  10. Hi I have a question and cross my fingers if you can provide any help.

    The 1 spreadsheet has daily data (B column) with date in the form of dd/mm/yy (e.g 1/1/2018....16/08/2021) (A column).
    The 2 spreadsheet has only years (2018..2021) (A column).
    I want to use a formula that will match the data that is the latest date of each year to the 2 spreadsheet to the respective year. (B column in 2nd spreadsheet) .

    Thank you in advance. If you have any advice i am grateful .

  11. Hi I have a question and cross my fingers if you can provide any help.

    I have two spreadsheets with dates and data

    The 1 spreadsheet has daily data (B column) with date in the form of dd/mm/yy (e.g 1/1/2018....16/08/2021) (A column).
    The 2 spreadsheet has only years (2018..2021) (A column).
    I want to use a formula that will match the data that is the latest date of each year to the 2 spreadsheet to the respective year. (B column in 2nd spreadsheet) .

    Thank you in advance. If you have any advice i am grateful .

  12. I have 2 definition of dates in one cell which is the Post dated and next working day, what formula can I use if I choose post dated on a cell and then the other cell will automatically choose same day and same as the next working day..thank you

    1. Hi!
      You write: "I have 2 definitions of dates in one cell". If there are 2 dates in a cell, then this is text, not a date.

  13. How to do the formula of the following example;

    Transaction Date Type of Customer Due Date
    20-July-2021 Regular 4-Aug-2021 Add 15days if regular
    20-July-2021 Consignment 27-July-2021 Add 7days if consignment

  14. Hi what formula can i use for countif if i want it to count items completed on a specific month. data on excel are dates. here is what i tried kindly let me know how to modify countif(Sheet8!I2:I222,">" & date (2020,8,1),"<"& date(2020,9,1))

  15. Hello, i need a help with a formula to automatically add a due date based on an assignment date on column A (1 Jan 2021) and a text in column B (Red) then column C to have a due date 90 days from the date on column A (1 Jan 2021.

    1. Hello!
      Please check out this article to learn how to add days, weeks, months or years to date.
      For example, to add 90 days

      =IF(B1="Red",A1+90,A1)

      I hope this will help

  16. I am trying to figure out a formula for the following:

    If anything in (A) column=5, then take dates in (B) column And add 14 years, 1 month and subtract 1 day, And if anything in (A) column=4, then take dates in (B) column And add 8 years, 1 month and subtract 1 day.

    Any help would be appreciated

  17. Hello, I need the formula which will produce a future date when I input a number of weeks
    Column A: Date
    Column B: Lead time (number of weeks)
    Column C: I need the future date in here

    Thank you!

  18. ACTIVITY_FROM ACTIVITY_TO SPOT_STATUS Days total amount
    2/5/2021 4/5/2021 RO Printed 60 200000
    i want month of Feb how much ?
    I want month of Mar how much ?

      1. Data sample-
        If i have joined any company from 13 Jan to 8th July 2020 and there is monthly salary is 15000
        then how to calculate 19 days for Jan also Feb/Mar/Apr/May/June/and 8 days July salary?

        Column ref-:
        A= 13 Jan 20
        B=8th July 20
        C= 15000 Monthly
        Now need to calculate in Monthly-:
        D= Jan month ?
        E=Feb month?
        going forward till 8th july
        Thanks
        Purna

        1. Hello!
          If cell A1 contains the start date of work (for example, January 13), then use the formula

          =15000*(EOMONTH(A1,0)-A1)/(EOMONTH(A1,0)-EOMONTH(A1,-1))

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

          1. Great thanks worked this function.

  19. I need a formula to look at two different dates in two cells and choose the latest date of those two to then add 26 weeks to and come up with a new date.

  20. I have company 's code no in column A. I have one date i. E. 01/04/2018 in column B. There is range of date. 01/04/2015,01/04/2016,01/04/2017,01/04/2018,01/04/2019 in column C. In column D, there is range of companies code no. I. E ABC, BCD, CDE, DEF Now I need to match particular company' s code and particular date with range of dates and company's code. How to do this.
    Pl guide me.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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