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

  1. Hello, I have the following data: Cities, order creation dates and order confirmation dates. I need to count the number of orders in each city with the same order and confirmation date. I'm counting with the countifs function, but I can't figure out where the problem could be, maybe I'm putting the function together wrong?

      1. I have tried many ways but I am not getting any result. How should the function look like to calculate how many values ​​there are when the creation date and the verified date is the same:

        Column A - City name, Column B - Creation date, Column C - Verified date, my function look like this: =COUNTIFS(A:A,G16,B:B,"=C2")

        G16 is the city name for which all values ​​satisfying the condition must be counted. The date format is Short date.

        1. Hi!
          Look closely at the syntax of the COUNTIFS function. The = sign is not used in function arguments. Also take a look at the link I gave you earlier.

          =COUNTIFS(A:A,G16,B:B,C2)

  2. Hi,

    I used the Excel DATE function to calculate dates for example (=DATE(YEAR(D4)+1,MONTH(D4),DAY(D4)) so when I enter say 1/1/2022 the next cell will automatically say 1/1/2023

    Can I auto fill the formula to a blank cell? When I auto fill it to a blank cell a date (1/1/1900) appears. I want the cell to remain a blank cell but still have the formula in it so when someone enters a date the next dates will automatically appear. So if I enter say 1/1/22 into one cell 1/1/23 will appear next to it and so on.

    Is this possible or will I have to put up with 1/1/1900 until I enter a date into the cell?

    1. Hello!
      Use conditional formatting in a cell with a condition formula

      =D4=""

      Set a custom number format in conditional formatting to not show zero: #,##0;-#,##0;
      Add a condition to your formula:

      =IF(D4="","",DATE(YEAR(D4)+1,MONTH(D4),DAY(D4)))

      I hope I answered your question. If something is still unclear, please feel free to ask.

  3. Sir I have little complex requirement

    In my below statement of account I need to pull the closing balance of any particular date how it will be possible, please help

    below is my example

    Date Description Debit Credit Balance
    05/11/2022 Opening balance 6,200.00 6,200.00
    05/11/2022 Paid to Raju 230.00 5,970.00
    05/11/2022 Received from Vijay 550.00 6,520.00
    06/11/2022 Cash Collection 8,345.00 14,865.00
    06/11/2022 Food Expenses 258.00 14,607.00
    06/11/2022 Travelling Expenses 879.00 13,728.00
    06/11/2022 Paid to Stanly 980.00 12,748.00
    07/11/2022 Trafic Fine 85.00 12,663.00
    07/11/2022 Cash Collection 3,330.00 15,993.00

    In cell my date is 6/11/2022 and in cell i need the result of closing balance of 6/11/2022, that is 12,748.00

    How I can pull the closing balance from statement of account of any particular date

    Please help me.

    With regards,

    Sunil Pinto.

  4. Hello, I am trying to extract just the date from this format: 2022-01-01 13:23:59 +0000

    I have tried to classic formulas but they don't seem to be recognising it. I have over 2,000 rows of data and so I need a quick fix to extract date only please?

    Thank you!

  5. .. lll
    How I can get a different information of
    different employees in different date in one
    cell?

  6. I need a condition like previous month of 9th to current date is this possible in excel and every time previous month will be changed like this is october then the previous moth is september if the month is december then need to extract data from november 9th to current date

  7. When using the formula =datedif(A4,B4"m") the formula shorts me 1 month, example if I entered 01/01/2023-12/31/2023 the formula says that is 11 months not 12, how do I correct this?

    1. Hi!
      It has been said many times here that 12/31/2023 means 12/31/2023 00:00:00. Therefore, the date 12/31/2023 is not included in your time slot.

  8. Hi,
    I need to set up formula, that shows if "start date" less than 3 days from TODAY and show me urgent alert.
    thank you,
    S

  9. how can i sum a column with a date range like for month of September and for one particular name(like how much bob spent in month of September 1 to 30th)

  10. Sir i need one help, exm: I have one date (20-Sep-2022) in A2 column, in A3 column i write down number 66 (days), i need date result after 66 days on A4 column. can you please help me this unction.

    A2 A3 A4
    20-Sep-2022 66 (show date after 66 days)

    Thanks and regards
    bdmauz

  11. Column J contains the date received. Column V (Review Date) has a drop down selection of "4 Months", "6 Months", "12 Months", "Indefinite". Basically I need a formula that will show the date to be reviewed in Column V depending on the selected drop down option. For "Indefinite" I want the cell to populate as 'Indefinte".

    Thanks in advance.

      1. Sorry, to be more clear. Say I received a notice on the 30/06/2022 and wished to review the notice in 4 months time. I would put 30/06/2022 in Column J (review date) and select "4 months" from the drop down in Column V (review date). My problem is that I need Column V to show the actual date to be reviewed after I select the option from the drop down, for example in this case it would be 30/10/2022 after selecting "4 Months". Is there a way to do this?

        1. Hi!
          As I said, the drop-down list will interfere with the cell in which it is located, the selected value. Based on this value in the neighboring cell, you can use the IF function with multiple conditions to make the necessary calculation.
          For example,

          =IF(V1="4 months",DATE(YEAR(J1), MONTH(J1) + 4, DAY(J1)), IF(V1="6 months",DATE(YEAR(J1), MONTH(J1) + 6, DAY(J1)),""))

          For more information, please read: Add months to date in Excel.

          1. Thanks for reviewing this one for me!

  12. Hello!

    I have a formula in column M (=$G3-35) to populate a due date. What formatting rule would I use to make the dates in column M turn red when the due date is at 35 days or less?

    Thank you!

  13. (Start Date) 12-01-2022) (End Date 02-02-2022)=IF(AND(MONTH(G6)>=MONTH(A7),MONTH(G6)<=MONTH(B7)),
    IF(MONTH(G6)=MONTH(A7),DAY(EOMONTH(A7,0))-DAY(A7)+1,
    IF(MONTH(G6)=MONTH(B7),DAY(DAY(B7)),DAY(EOMONTH(G6,0)))),0)

    Dear sir,.
    like this i entered the formula in excel it works for calculating between two month day as per month but if the month of start and end date in the same month its not calculating kindly help please
    It is for planing enginering to entered work down as per start and end date as per days of month
    kindly request to help i am stuck in this please.........

      1. Dear sir,
        i Want to distrubute any quantity as per month days compare to start and end date if that start and end date is in between more than one month to be calculate how much days in each month

  14. Hello. I am using Microsoft Office Pro Plus 2016. I am creating a worksheet that contains NAMES in Col. A and BIRTHDATES in Col. B. I have formatted the BIRTHDATE column as Custom m/d. Everything works well until I attempt to insert 2/29 in the cell. It quickly replaces the input with 2/1. I can insert 2/28 and it will accept this input. How can I make it accept the 2/29 date input?

  15. I have a sheet (handicaps) that contains names, golf handicap (numerical). This sheet changes each week of our season. I have another sheet (competition) that when the name is placed in a cell it looks at (handicaps) for the correct number for that week. My problem is we have 20 weeks, each week we have a competitive match. Once the number is inserted each week I have to manually copy and paste the true value. Each week a persons handicap can change and if I do not put in the true value after my formula retrieves the number it is possible 2 or 3 weeks down the road the handicap in week one could change because the formula is still active if I do not copy and paste values. This would change results from past weeks.
    So, I am looking at calculating my weekly columns by date, some how. Something like calculate formula only between May 4, 2022 to May 6, 2022 only. Then in the second week go from May11, 2022 to May 13, 2022 only. That way I could imbed all my formulas and only calculate in the time frame that I want.
    This way I would not have to copy and past values each week and my handicaps can fluctuate with no concern that it will change any past or future calculations.
    Thanks

  16. Hello!

    I have what seems like a simple google sheet/excel date function that I can't quite figure out.

    I have a drop down menu with a "yes" option in column A. In column B, for each row, I want it to display todays date on the day the "yes" was selected in column A. is this possible?

  17. how to convert Aug / 19/2018 13: 39: 09
    to 20/08/2018 10:14 format ? anyone can help me

  18. I need to highlight the date in 1 column that is 14 days greater than the date in another column. What is the best method for doing that?

    Example: Col D date is 24-Mar-22; and Col E date is 17-Apr-22. I need to determine and highlight Col E if it is 14 days past the date in Col D.

    Thanks!

      1. Yes, that works great! Better than the =Days(E2,D2) and then looking to see which rows have 14 or more days between them

  19. How do I count the number of years worked since a specific date while accounting for a start date after the specific date?

    Kind of like "Years worked since "A Date" if start date is "B Date"

      1. It didn't unfortunately. I've been searching for a formula for this for 2 days. I may just have to work it out myself.

  20. hi I want to count how many cells are in column B past due

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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