Comments on: Excel date functions with formula examples

This is the final part of our Excel Date Tutorial that offers an overview of all Excel date functions, explains their basic uses and provides lots of formula examples. Continue reading →

Comments page 13. Total comments: 595

  1. i am trying to calculate month and days difference in two days
    cell A1 07/07/2016
    cell b1 07/10/2016

    formula
    =ROUNDDOWN((B1-A1)/365,0)&" "&"Year"&" "&TEXT(B1-A1,"mm")&" "&"months"&" "&TEXT(B1-A1,"D")&" "&"Days"

    output 0 Year 04 months 1 Days
    answer should b 3 month why it is calculating wrong days and month

    plz reply

    if possible plz let me know can i use text function for year difference also?

  2. Hi, I'm looking the formula for a "date and time of completion". For example.

    Start Date and time 13Nov2016 12:30

    Cargo onboard 2000 bbls
    Rate/hr : 50 bbls/hr

  3. I am trying to make any dates before today's date change into 9/9/999. If the date is today or after, I want it to stay as is. I have been using this function and it does not work.

    =IF(U2<TODAY(), "9/9/9999", U2)

    Please help. And by the way, when I put data into these cells, it just deletes the formula. This issue seems like an Excel 101 issue I can't find a solution online for. Please help.

    1. Hi Matt,

      You cannot have a formula perform a function on itself (the same cell). You have to enter the formula in a different cell and it will perform the function you are attempting to do. So, say the dates you are entering are in column A. If you create column B next to it, and then have the formula you are using refer to the values in column A, you will get the result you are looking for.

      Example: Enter 1/15/1995 in cell A1. Then in cell B1 enter =IF(A1<TODAY(), "9/9/9999", A1) . Cell B1 will display the date if it is today or in the future, and will display 9/9/9999 if it is in the past.

  4. Hi,

    Could you please advise me.

    I have a staff hollidays sheet.
    First collumn with the staff name and first row with callendar dates.
    Each hollidays days is marked with a h, and with a countif formula is count the total.

    What i need is a formula that would work as if would be the countif formula, but counting until the date of today.

    Generally speaking would be something as, How many h do we have until the calendar date that we are on.

    Kind regards,
    Irina Lopes

  5. Please can you help, I need to set up a spread sheet and need to have a column that shows that days date when a Y is put into another cell, can this be done and if so what would the sum be.

    Many thanks

  6. I am trying to build a spreadsheet of folks showing EOD dates (Entry on Duty). What I would like the formula to do is change their available leave automatically once they reach their 3 or 15 years of service. In other words, before the 3 years you only accumulate 4 days of leave whereas after 3 years it will go up to 6 days, and 15 yrs. to 8 days.
    Is this option possible?
    Many thanks in advance.

  7. Hi,

    I am working on a database using excel, and trying to get some conditional formatting up and going.

    Basically, I would like an entire row to be highlighted if one cell in that row (date format) returns a date within a certain year (or even within the past year).

    I've only just begun using conditional formatting, so apologies if this is not the place to ask!

  8. Hi,

    I need to get the number of Days (day 2 - day 1 below), starting from today:

    day 2: the previos working day (yesterday, or if yesterday was not a working day, the last working day before that).

    day 1: the working day before day 2.

    If possible with regards to hollidays (which I assum must be in a separate list)

    Thnks all, Tomas

  9. Name ID Date
    Sagar 123 9/26/2016
    Sagar 123 9/27/2016
    Sagar 123 9/28/2016
    Raghu 234 9/26/2016
    Raghu 234 9/27/2016
    Raghu 234 9/28/2016

    In next column, I need the largest date to be displayed for every person. After displaying the date, 2 cells should be empty and again largest date for 2nd name. Again 2 cells will be empty and largest date for 3rd person. I need result looking like that. Is there any formula?

  10. PLEASE HELP!

    I'm trying new ways to use excel and I saw someone have a roster up that automatically changes the day in the column.

    How do I achieve this? Do I use a formula or macro?

    I'm so confused but amazed at the same time.

    Any help would be greatly appreciated :)

  11. Hello,

    I have tried using the datedif function in many different ways to calculate the total months difference in between two cells values. It has to include the month it is in, and the month it is ending in (oct 2016). So for example, it shows Paid through, and always ends in Oct 2016. So this would be 9 months. Including Feb, to Oct. If anyone can help in regard to this it would be appreciated!! I keep getting #Name? and #Value errors.

    Paid Through October 2016 Total Months Difference
    1-Feb-16 1-Oct-16 #NAME?
    2017-03 2016-10 #VALUE!
    2017-02
    2016-10
    2016-10
    2016-09

  12. Need any help you can provide for following situation.

    I'm maintaining a yearly Excel spreadsheet for 1 Dec - 31 Oct to account for a variety of activities. Trying to provide Quarterly and Annual summaries has required yearly change of 95 comparison functions since I don't understand how to compare 1-Dec-2015 through 28-Feb-2016 without first determining the data equivalent of each date, e.g., 42339 and 42428 respectively. Solution has been (for 1st Quarter):
    =SUMIFS(Contacts!$K$3:$K$5002,Contacts!$D$3:$D$5002,">=42339",Contacts!$D$3:$D$5002,"<=42428")

    How can I just compare each transaction with a 1-Dec-2015 and a 28-Feb-2016? Or more sustainable comparison. And, once accomplished, annual update of spreadsheet would allow blanket change of applicable respective year(s) instead of having to change all 95 individually. Would greatly appreciate your assistance.

    Larry

  13. I would like to know that can excel calculate and show the date of Friday before that day or not.
    For example,

    A B
    Customer required shipment date 11-Jan-2017
    Shipment Date that we need to ship 21-Nov-2016 (I use formula B1-51)

    However, The loading date of the shipment needs to be on Friday. So, we would like to know the
    formula that can calculate the Friday date before 21-Nov-2016 which is 18-Nov-2016.

    Please kindly advice whether it have the formula to calculate this kind of problem or not.

  14. i want a formulation in excel

    design target design to costing send date costing target
    05-04-2016 07-04-2016 09-04-2016

    if first dept. not able to achieve there target date then next department will allocate delays no of days as per design to costing send date

  15. Hi,

    I want date of every Monday of every month when Monday came, date can be changed automatically. Please Help.

  16. I have a spreadsheet that needs to have a date highlighted (column A) if it exceeds the 48 hour due date (column C -hidden and auto calculated) IF a completion date is not located in Column D. I would also need the highlight reverted once a completion date is added to column D. any thoughts? This spreadsheet has blank, formatted rows below for when additional items are added. Any ideas? Thank you!!

    1. This is what I have currently, but nothing is changing, and many items should be highlighted RED now...
      ="IF($C>=TODAY),($D=ISBLANK)"

  17. I have 2 columns target date e.g Dec-16 and Actual date e.g Dec- 16, what I need is to create RAG status in a 3rd column
    So if column g is equal to column h gives me An A
    if column g is less than column H gives me a G
    if column g is greater than column H gives me a R
    so if I could ask for assistance on a formula as going round in circles trying IF etc

  18. Hi,Svetlana Cheusheva
    what formula shall I apply to minus a fraction from a date? ie; 2/9/2016 - 1/3 or 2/9/2016 * 1/3 or 2/9/2016 + 1/3..
    what will be the next date if such is calculated?

    thanks

  19. Hi,

    I have drop list (monthly, quarterly and annually) I want the end date of the contract automatically changed based on my selection of the drop list

    If I choose monthly, the end date will be 1 month after the effective date
    If I choose quarterly, the end date will be 3 months after the effective date

    and so on

  20. I am not sure if a question similar to this has been answered. I need to calculate dates pertaining to the service and filing of certain documents with a court.

    Here are the big factors:

    The document has to be filed with the Courthouse on the "Entry Day." The entry day is ALWAYS a Monday and MUST be a Monday.

    In order for the filing to be accepted by the Court, it has to have been served upon the other party between 7 and 30 days before the Entry Day. We can call this the "Service Period."

    So, using an Entry Day of 8/29/2016, the document would need to have been served on the other side sometime between 7/30/2016 and 8/22/2016.

    The "Trial Date" is always 10 days AFTER the Entry Day and is always a Thursday. Using this example, the Trial Date would be 9/8/2016.

    The other side must respond 7 days after the Entry Date. This is the Answer Date and is also always a Monday. Note: The Answer Date is also 3 days before the Trial Date.

    I have put together a formula that can calculate all the dates I need to know based on me inputting a possible Entry Date. It will then tell me the Service Period, the Trial Date, and the Answer Date. However, this requires me to first look up on a calendar the next few Mondays and then to, by trial and error, plug in potential Entry Dates to see if I am still in the Service Period window.

    What I would like to know is if it is possible to be able to plug in today's date and have all the potential dates calculated for me. However, the key is that the formula must always account for the fact that the Entry Date must always be on a Monday and that the Service Period must end 7 days before the Entry Date (also on a Monday), and that the end of the Service Period cannot be a date that has already passed. If the end of the service period has already passed, I would like it to move all the dates forward to the following week.

    For example, I would like that if I put in today's date of 8/18/2016, the formula would recognize that the next Monday (8/22/2016) is less than 7 days away (in this case, it's 4 days away) and therefore, Monday 8/22/2016 could not be a valid Entry Date as it would violate the Service Period requirement. Therefore, it would then automatically make Monday 8/29/2016 the Entry Date and base all other dates upon that date.

    Sorry if this is not clear.

    Thank you very much.

  21. hi,

    how can i make a format like this?

    departure , rejoining , "on leave, working"

    12 aug 16 , 2 sep 16 = the excel will only show that " on leave or working

    thank you.

  22. i am handling a tracker and i need the due date column as 20 months from current date.suggest me the formula

  23. Hi Sir, I really need help on my excel. I downloaded an excel report from one of our tracker system tool. I sent it to my costumers and when they received it, the excel contains future dates. I cross checked my excel file but it has no future dates, I really don't know the issue here. Please help me :)

  24. I am making a trial calendar for my law firm. I need to calculate, for example, the date of trial -100 days. They need to be calendar days, not work days, and I have already set up a list of holidays for the next two years. I cannot figure out how to do the formula for the date -100 CALENDAR days, including holidays. Can someone please help me. I have been working on this for days. I have it totally figured out for the dates that need to be WORK DAY, but cannot figure out the ones for calendar days. Any help would be greatly appreciated. NOTE: I am working in Excel 2003.

  25. Hello, can anyone help me what is excel formula if the date will tell it is overdue in: equal or less than 3 months, greater than 3 months, greater than or equal to 6 months?

  26. I want to count the number of cells that are before or after a specified date.

  27. Hello,

    I have two dates in two different cells (A1 = 4/12/1993 and B1 = 04/05/1993) and i want to verify if they fall with the same quarter (89 days). If two dates are within the 89 days, the data "passed" if outside of 89 days, it fails...

    thanks for your help....

  28. Hie is there any formula to track the date occurring in next 2 months?

  29. Hi i need a formula that will check the day is >14, the month is > 7, anfd the year = 16 inserted using the 'TODAY()' function. if the result =TRUE, then insert 500 otherwise enter ""

  30. How about when you are about to get the the formula on what day of the week will your 100th birthday fall?

  31. hi can you help me?

    IF AG3 IS 2 DAYS BEFORE DELIVERY DATE THE RESULT IS "DELIVERY DATE" IF AFTER DELIVERY DATE THE RESULT IS "DONE" WHEN AG3 HAS NO DATE THE RESULT IS "NO SUPPLIER DELIVERY DATE" OTHERWISE "ON GOING

  32. if in cell B1 i have a date 3/23/16 and in cell B2 5/23/16 another date and in cell B3 4/23/16 another.i need to work out if the today's date is 6/23/16 being cell A1, then if cells B1,B2 & B3 is smaller than 30days then put it in cell D8 if they are 31days and less than 60 then put it into D7 and if it is 61 days and less than 90days then put it into D6.

  33. what i need to know is

    12 JULY 16 is todays date.
    18 JULY 16 is the date where i need the follow up.
    i subtracted 12JULY16 - 18JULY16 = 6

    NOW I NEED THIS NUMERIC 6 to get less day by day till the 18TH JULY 16 arrives

    any formula ?

    1. Hello Muhammad,

      You can use a formula similar to this:
      =A1-today()

      Where A1 is the follow-up date.

  34. Dear Madam / Sir

    Good Morning..

    How check Average In Caller ( It's A Grade / B Grade )

    Ex..

    Caller Name - XYZ
    Calles Made - 120
    Calls Connective - 70
    Appt - 10
    Turn up -5

    Please Explain Me How to Calculate Caller Average

  35. Hi i just want to know to summarize the value of activity performed as on date today in columns at the start of the table. how can we do ??

    1. I would like to prepare data like this with dates on the next columns

      District Total systems Total AMC completed till Yesterday Total AMC completed today 01 02 03 04 05....

  36. My question is i have set of rows having date timestamps across 2013 to 2016. as 01-mar-2014 00:07, 04-Jul-2015 06:40 and so on.. My requirement is I need to extract only Month and Year alone so as to do a pivot on it. It can be done using text as text(A10,"mmm-yyyy"), but a pivot on this data will return the text values not sorted one as Jan-2013,Feb-2013 ... May-2016. If i use date value and then do a pivot on this column, it gives me only list of month not month-year as Jan,Feb,Mar and so on.
    Please help.

    1. Let me be clear with the requirement. I need pivot on date field . it should display the values as below
      Jan-2013 30
      Feb-2013 32
      Mar-2013 40
      ...
      Jan-2016 30
      Feb-2016 10
      Mar-2016 02

      Instead of displaying as below
      Jan 60
      Feb 42
      Mar 42

      I dont want to use group function in pivot (Group by Moth,year).Coz i have to do various calculation based on this value.
      Hope you got my requirement.

      1. Dear Team,
        I got it. I applied same logic. Converted date to text as text(A10,"mmm-yyyy"). Then took date value. this time it returned with year. After which i applied pivot on it. Now am getting as
        Jan-2013 30
        .
        .
        .
        Mar-2016 02

        Many Thanks.

  37. 1.First thing I need how to show like this "21-jun-16 to 24-jun-16" in a single cell in excel.

    2.Second Thing how I need to calculate the values of between the dates of 21-jun16 to 24- jun-16 which is in another work sheet.

    1. Dear Ravi,

      1. To display "21-jun-16 to 24-jun-16", type it out in a cell and click enter.
      I don't think you have expressed your query properly.

      2. To calculate values between dates, use the below formula.
      =Sheet2!A1-Sheet2!B1+1
      Change the cell references as required.
      I have used Cells A1 and B1 of Sheet2 for the dates.

      Vijaykumar Shetye, Goa, India

  38. Hello Svetlana,

    I'm creating spread sheet where I would like to for eg. in B3 cell place a date of the project to start then in another cells will automatically change to name of the month, another will change to "day number of the month", 1, 2, 3 and so on, and another cell below will change to name of that day but single letter only (instead of Monday just M, T, W, and so on)

    Will you be able to help me achieve my idea?

    Best regards
    Michael Nosek

    1. Michael Nosek

      (1) For display of the month number, change the format of the cell.
      Go to
      Home - Format Cells - Number - Custom - Type
      and enter the letter m
      The date value in the cell will not change, but the month number will be displayed.

      (2) For display of the fist letter of the weekday, use the formula.
      =CHOOSE(WEEKDAY(A2,1),"S","M","T","W","T","F","S")

      Regards,

      Vijaykumar Shetye, Goa, India

  39. I am trying to create a worksheet that will provide the month of a first shipment based upon the day of the month that a product was ordered. i.e if prior to the 5th of the month the first shipment will fall within that month. After the 5th, the first shipment will be sent the following month. Additionally, I need to determine the monthly shipping schedule based on the first shipment and the product frequency purchased. The frequency options are monthly, quarterly or bi-monthly (every other month).

    Can this be done with a series of date functions?

    Thank you so much for your help!

  40. Hi-
    What formula could I use to determine if a 'milestone' anniversary date (5,10,15,20 years, etc.) is reached within a quarter?
    So say my anniversary date is 4/6/1980 and I want to know if a milestone was reached between during the 3rd quarter current year (July 1 & Sept 30, 2015) - how can I calculate that?
    Thank you!

    1. Dear Dan,
      Use the below formula.
      =IF(AND(MOD(ABS(YEAR(TODAY())-YEAR(A1)),5)=0,ROUNDUP(MONTH(A1)/3,0)=ROUNDUP(MONTH(TODAY())/3,0)),"Milestone Quarter","-")

      For every milestone quarter, it will display "Milestone Quarter".

      Vijaykumar Shetye, Goa, India

  41. Hi!

    I'm using the =EDATE(A4,6) function. However, in my A4 cell, there is not yet a date but the function returns 182, which is 6 months from 0 (nothing in the cell). How do I get the cell that has the EDATE function to show nothing until a date is input into A4?

    Thank you!

    1. Hi Vanessa,

      Just add an IF function that checks for blank cells, for example:

      =IF(A4="", "", EDATE(A4,6)

  42. I am creating a protected 60-day calendar. Cell E2 is unlocked for a date input. The calendar week starts on Monday, going through Sunday (A7-G7). The calendar grid is A8-G8 all the way down to A17-G17. I want to be able to input a date in E2, (ie:5/10/16) and for excel to know that 5/10/16 is a Tuesday, so it automatically inserts 5/10 in the cell B8. I can then formulate for the autofil of the rest of the dates all the way through the end of the 60 days.
    I just cant remember how I set this up before, where excel knew which day (monday-sunday) to start the calendar by entering a date in E2...

  43. Hi,

    M having attendance data like check IN time in "A1" column Date is 16-Apr-16 and "B2" column Time is "17:55:00" and check Out date in "C1" column is "17-04-2016" and "D1" column Time is 00:53:00. Please let me know what is total duration of working hours. We require like HH:MM format.

    1. Dear Yogesh Zagade,

      Use the below formula, and format the cell in whichever format you require.
      =D1-B2+C1-A1

      Vijaykumar Shetye,Goa, India

  44. Hi,

    I have a monthly budget, and would like the due date to change automatically. For example, if payment is due on 5/3/2016, the following day, I want the date to change to 6/3/2016 automatically. Can you help?

    1. Dear Lester,

      Use the below formula
      =MAX(A1,TODAY())

      It will display the due date (5/3/2016), which is entered in cell A1, till the current date (in your case 5/3/16).
      After that, it will start displaying the current date (6/3/16 onwards), every time the file calculates.

      Vijaykumar Shetye, Goa, India

  45. If you want to eliminate the negative sign, then use the function
    ABS (absolute) with your formula.
    Example
    =ABS(TODAY()-A1) or
    =ABS(A1-TODAY())

    Vijaykumar Shetye,
    Goa, India

  46. I am trying to return the number of days from a set of dates (past and future) and todays date but display the resulting number of days prefixed with + (future) or - (past. Thanks.

  47. Please help me find a formula to calculate the date it will be in 60 days (with custom holiday dates removed). I've been stumped with this. Thanks in advance!

  48. I would like to know how to set a field to give me the next day after TODAYS date that is a certain day i.e, i want the next Thursday after today, or the next monday. etc. that auto updates when i open the spreadsheet.

    1. FORMULA 1
      =IF((7-WEEKDAY(A1,14)+1)=7,A1,A1+1+7-WEEKDAY(A1,14))
      Shows the date of the current Thursday, till the end of the Thursday, and the next Thursday, after the end of Thursday.
      Reference of date is in cell A1.

      To change the day of week from Thursday to any other day, change the value 14 in the cell to
      11 for Monday, 12 for Tuesday, ... 17 for Sunday.

      FORMULA 2
      =IF((7-WEEKDAY(TODAY(),14)+1)=7,TODAY(),TODAY()+1+7-WEEKDAY(TODAY(),14))
      Shows the date of the current Thursday, till the end of the day, and the next Thursday, after the end of Thursday.
      Automatically calculates for Today.

      FORMULA 3
      =IF((7-WEEKDAY(A1,1&$G$1)+1)=7,A1,A1+1+7-WEEKDAY(A1,1&$G$1))
      Shows the date of the current Thursday, till the end of the day, and the next Thursday, after the end of Thursday.
      Automatically calculates for Today.
      Weekday to be entered in cell G1 as follows,
      1 for Monday, 2 for Tuesday,... 7 for Sunday.

      Kindly change the cell references in the above formulas as required.

      Vijaykumar Shetye,
      Goa, India

  49. Hello,
    I have a spread sheet with a Header in D1 (Issue date). I am trying to get result in Column F (Review) of "Not Due" if the date is less than 640 days from issue, "Due" if the date is between 641 to 720 days from issue, and finally "Over Due" if it is greater than 721 days from issue. I have been trying the IF function and can only seem to get 2 returns but not the third. Thanking you in advance.
    David.

    1. I think I've got it;
      =IF(OR(D2=""),"",IF(D2>=TODAY()-638,"Not Due",IF(D2>=TODAY()-731,"Needs Review","Over Due")))
      I have then applied Conditional formatting so that Not Due = Green, Needs Review = Yellow, and Over Due = Red
      Seems to work Ok.

      1. The formula will work correctly, but you may include a few changes in the same.

        (1) The 'OR' function which you have used, is meant for checking whether 2 or more arguments are True. In your case, there is only 1 argument which 'OR' is checking. Hence it serves o purpose.

        (2) Today()-638 or (any date) minus (any number) could possibly give us negative values, if the number being subtracted is sufficiently large. The possibility could be avoided by adding 638 to D2, instead of subtracting it from Today().

        I have edited your formula as below.

        =IF(D2="","",IF(D2+638>=TODAY(),"Not Due",IF(D2+731>=TODAY(),"Needs Review","Over Due")))

        Vijaykumar Shetye,
        Goa, India

  50. I have an excel with dates in Col A and Dates in Col B with a value in Col C

    8/4/2015 8/4/2015 3703
    8/5/2015 8/7/2015 3705
    8/6/2015 8/10/2015 3708
    8/7/2015 8/11/2015 3715
    8/8/2015 8/12/2015 3728
    8/9/2015 8/13/2015 3731

    I would like to move the dates in Col B along with the value (which represents how many students made enquiries for our programs on that day) in Col C to line up with the date in Col A. Is there a formula for such an endeavor

    1. Assuming that your data is in cells A1 to C6,
      Paste the following formula in cell A7
      =TEXT(B1,"dd/mm/yyyy")&" "&C1

      The displayed result will be
      08/04/2015 3703

      Is this what you want to do?

      Vijaykumar Shetye,
      Goa, India

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