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

  1. I want to enter a formula that debits an amount based on date.
    Cell A1 = todays date. Other cell rows have transactions posted which i do want to happen unless it is at or past cell A1. Thank you

    1. Hello, Marc,

      could you please clarify what values you have in other cells? Are those some dates that you want to compare to A1?

  2. Looking for a function where for eg you type in a statement date (30/06/2017) it will pick up everything in the other worksheets pertaining to that particular date/month - what formula do I use...that's if I am making any sense:)

    1. Hello, Mel,

      Well, this is an interesting questions, since we don't know how your lookup data is stored and whether you want to pick up a row/a column or something other than that.

      For now, I can advise you to take a look at a few functions that may help:
      VLOOKUP
      HLOOKUP
      IF
      INDEX/MATCH

      Or, a simpler method is to try our Merge Tables Wizard that can do that for you :)

  3. Hi,

    I have a problem in calculating the EOSB using IF funct.
    We have system which generates the calculation for us but I want excel function to help double checking my results.

    The rules are as follows:

    5 years gets full salary

    thanks

    1. Hi,

      I have a problem in calculating the EOSB using IF funct.
      We have system which generates the calculation for us but I want excel function to help double checking my results.

      The rules are as follows:

      5 years gets full salary

      thanks

  4. Hi,
    Please let us know how can freeze the today formula in excel 2007

  5. MY INVESTMENT DATE IS 25/05/2016 AND ITS MATURE AFTER 779 DAYS HOW CAN I FIND EXACT DATE WITH EXCEL

  6. Hi Team,

    Please help me the below query.

    1. A column I have date like 01/01/2017 and B column I have date as a 01/15/2017. with same month date I have in A, B column it needs be red color(within Month). or else example like A column I have 01/01/2017 and B column have 01/02/2017 it needs to be yellow color .

    Please help on this thanks in advance.

    Regards,
    Reddeppa.

    1. The article you need is about formatting rules for dates. This part of it, to be more precise. There you will find detailed instructions on how to format cells that contain the same month.

  7. Column G is a due date with a conditional formatting =AND(H9"Due",G9<TODAY()), so that it is highlighted once it is due.
    Column H is the status "Open", "Due", "Done". I want Column H to auto populate "Open" or "Due" based on the date in Column G.
    Help

    1. Hey, Ellen,

      if the date is situated in G1, use the following formula for column H:
      =IF($G1<TODAY(),"DUE","OPEN")
      If this won't help, please, specify the dates in your G column.

  8. 0606
    0407
    0108
    0509
    0310
    1804
    2504
    0205
    0905
    1605
    2305
    3005
    1306
    2006
    2706
    1107
    1807
    2507
    0808
    1508
    2208
    2908
    1710
    3110
    Hi Svetlana, I have date format in Taxt (First tow Number are date and Last no are Months) as mentioned above and required date format as mentioned: 18-04-2017. Can you please help me with Formula to get this format please.

    1. Hi,
      This Side Rakhi, Firstly you just use Short cut key (Ctrl + !) after that select date format and after that you just use short cut key (Alt + D + E + E) now fixed width, Next, Next and use Text and Finish.

    2. Hi Rohan,

      Assuming the source data is in column A, beginning with cell A2, you can use the following formula to convert the text strings to dates:
      =DATE(YEAR(TODAY()), RIGHT(A2,2), LEFT(A2,2))

      The DATE function displays dates in the default short date format set in your Excel. To change the date format the way you want, please use the Format Cells dialog as explained in this tutorial:
      https://www.ablebits.com/office-addins-blog/change-date-format-excel/

      Or, you can nest the Date formula within the TEXT function and specify the desired format directly in the formula:
      =TEXT(DATE(YEAR(TODAY()), RIGHT(A2,2), LEFT(A2,2)), "dd-mm-yyyy")

      However, please keep in mind that TEXT always outputs a text string that only looks like a date.

  9. Hello,

    Please suggest formula for the below mention point :
    date of Birth 3-Apr-59
    Date of 60 year 3-Apr-19
    if Day grater then 1 then date of Retirement is 30th day of 60year date otherwise it is last day of previous month of 60year date.

    1. Suppose birthday in A1 cell
      Then use this formula

      =IF(DAY(A2)>1,DATE(YEAR(A2)+60,MONTH(A2)+1,1),EOMONTH(DATE(YEAR(A2)+60,MONTH(A2)-1,1),0))

  10. How do I calculate the number of Friday's between a current date and a future date?

    Thank you,

    1. Hello Leesha,

      You can use this formula:

      =INT((WEEKDAY(TODAY()-6)-TODAY()+A1)/7)

      In the formula, A1 is the future date, and 6 stands for Friday. If you want to count the number of some other day of the week between two dates, you can replace 6 with other numbers between 1 (Sunday) and 7 (Saturday).

  11. 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)"

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

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

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

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

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

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

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

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

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

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

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

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

  24. I have once Excel file in which i have 52 columns considering as weeks of the year. Then if the cell is equal to current date then i have to display the value from other sheet.Please help

    1. Dear Syed Raheemuddin,

      I have not understood your question. Please explain it in detail.

      If a cell is equal to current date, then how will you display the value form another sheet in the cell?

      When posting a question, please be very clear and concise.

      Vijaykumar Shetye, Goa, India

  25. hi everyone :)

    i have date format in see (1)
    need convert to this format in excel see (2)

    1) 16-03-2016 5:46 PM
    2) 3/12/2016 1:13:00 AM

    1. Go to Home - Format Cells - Number Type, and
      Change the format of the cell to
      d/mm/yyyy h:mm:ss AM/PM

      Vijaykumar Shetye,
      Goa, India

  26. Hi,

    I want to identify with the ageing of the current time by considering it as non communicating 6 hours

    Ex: 18-03-2016 12:28 (>6 hours forumla should show as "communicating")
    18-03-2016 12:28 (6< hours forumla should show as "noncommunicating")

    Thanks

    1. To make it more simple.

      Here is the date and time

      18-03-2016 12:37

      (=IF(L2>=TIME(20,59,59)+ TIME(21,0,0),"communicating","noncommunicating")

  27. Hi,

    I am interested in a date formula that will allow me to enter data into other fields and then having that date "stamped" when the data was entered

    example: in cell A1 have a name typed and then in A2 have today's date appear (and not change).

    Let me know

    Thank you

    1. Hello, Scott,

      Уou need a VBA script for this. Sorry, we cannot help you with it.

  28. 20180502
    20170101
    20180301
    20190802
    20170901
    20180601
    20170201
    20160601
    how to convert in to date yyyy-mm-dd 16'oct convert in 10-2016

    1. please help me,

      i have online software date format (14/03/2016 3:07 PM) i need to convert this format (Mar/14/2016)

      please help

      1. Dear Munawwer Khan,

        Select the cell and go to Home - Number - Custom - Type,
        and enter the below format
        mmm/dd/yyyy.

        The value of the cell will not change, but it will be displayed in the type of format you require.

        Vijaykumar Shetye, Goa, India

  29. I need a formula which when entered in a cell of a Colum will automatically add date and time base on the present date and time on the rows down the Colum as I populate other cells in an excel sheet.

    1. Hello,

      Looks like you need VBA for this. Sorry, we cannot help you with it.

  30. I have a date as 01/20/16 i need to change it to 01/20/2016? How do i do that?
    I tried using Date/Year function but no luck

    1. Hello, Vivek,

      Please try the following:
      right-click on the cell -> Format Cells... -> Date -> pick the format you like.

  31. Dear Sir / Madam

    Please Clarified Me , How Do The Calication In Excel

    No Saledate Km LastServiceDate Last Service Type
    1 01/02/2015 100Km 25/02/2015 1St Free
    2 02/05/2013 5000 Km 01/05/2014 2Nd Free Service

    Dear Madam / Sir

    1St Free From Sale Date To 60 Days
    2Nd Free Service From The Sale Date To 330 Days Or 1500 Km
    3Rd Free Service From Sale Date To 700 Days Days Or Below 7000 Km

    After Complete 3Rd Free Service It Will Show Paid Service ,

    Please Explain Me
    I Need Sale Date To Service And Last Service Date To Up Comeing Service Type

    MyNo Was - 9611786004
    Please Help Me How To Do.....

    1. Ok Madam

  32. Hi
    If cell A1 and B1 have date it should show completed, but it showing number of days pending, please clarify this!

    1. Manju,

      In your first message, the conditions were stated in this way: "If I entered date in Cell A1, cell C1 should Show today-A1 cell; if I entered date in B1 C1 cell should show Completed". The formula checks the conditions in this order, and as soon as the first condition is met, other conditions are not checked.

      Now, it you want to show "completed" when A1 and B1 have dates in them, use this one:

      =IF(AND(A1<>"", B1<>""), "Completed", IF(A1<>"",TODAY()-A1, "Don't worry"))

      If you want the formula to show "Completed" when there is a date in B1, regardless of whether there is or there is no date in A1, use this one:

      =IF(B1<>"", "Completed", IF(A1<>"",TODAY()-A1, "Don't worry"))

  33. If I entered date in Cell A1, cell C1 should Show today-A1 cell, if I entered date in B1 C1 cell should show Completed if A1 and B1 is blank C1 should show Don't worry.
    Please let me know formula for this details

    1. Hello Manju,

      Here you go:
      =IF(A1<>"",TODAY()-A1,IF(B1<>"","Completed","Don't worry"))

  34. hi am looking for a formula that I can use in excel to highlight customer details on their payment days e.g if a customer make payments on Saturday a highlight on her details

    1. Dear Esther,

      (1) To highlight the cells whose dates math the weekday of any given day, go to Home - Conditional formatting - New rules - Select a rule type - Use a formula to determine which cells to format - Edit the rule description
      In Edit the rule description, enter the below formula
      =WEEKDAY(TODAY())=WEEKDAY(A1)
      In Format - Fill, select the required colour.

      Is this what you want to do?

      (2) In case you want to highlight cells which matches the date on any given day, then type the below formula in Conditional Format.
      =a1=today()

      Regards,

      Vijaykumar Shetye, Goa, India

  35. Hi, I need to have (todays date - 3) calculated in mmddyy format and inserted into a text field (e.g. "ABC/mmddyy_received". I will be really impressed if that is possible.

    1. Hello Gordon,

      You can use the TEXT function to convert the current date to text in any desired format, and concatenate the returned value with other text, e.g.:

      ="ABC/"&TEXT(TODAY(),"mmddyy")&"_received"

  36. hi,

    I'm looking for a formula in which computes the current year minus the previous year. I'm creating a table in which automatically compute the number of years or months.

    example:

    employee 1 starts: August 1, 2009 - current year
    Employee 2 starts: Sept 1, 2015 - current year

    Thank you,

    1. Hi An,

      You can use the DATEDIF function to calculate the difference between two dates in days, months or years.

      If you are looking for something different, please specify exactly what results are expected in your example for employee 1 and employee 2.

  37. Is there a way to write a formula toe present yesterday to today? example present 8/13/2015 to 8/14/2015.

    1. Hi Tony,

      Sorry, I do not exactly understand the task. Do you want to "label" the dates as "present" (i.e. display the word "present" in another column in the same row) or are looking for something else?

  38. I wanted to set a now date for when i open the sheet to calculate the date and add another value to another cell if the date is greater than a set date of each month i.e late fee applied in cell B3 if date is over the 5th

    1. Forgot to mention that late fee applied in cell b3 if date is over the 5th but only if there is no value in A3.
      A3=Payment made B3=Late fee If date is Now date is greater then 5th of each month

  39. Thank you!

    It worked but P3 changes N2. Do you know what I've done wrong so that P3 changes N3 and so on

    Thanks
    Micky

    1. Micky,

      Make sure that the cell referenced in the formula corresponds to the first row in the rage to which the rule apples.

      I.e. if your apply the rule beginning with row 2, use the formula I provided, if you apply the rule beginning with row 1 then replace P2 with P1 in the formula, and so on.

  40. Hi,

    I have used conditional formatting in Column N so that when today's date is past the date in each cell it changes to bold, red text.

    In column P I have used conditional formatting to say that if I have tended to my outdated task as the following: equal to 'Y' is Green, equal to 'No answer' is yellow, equal to 'n' is red.

    My question is:

    If I enter either 'Y' or 'No answer' or 'N' into any cell in column P can this change the font in the corresponding cell in column 'N' back to regular, black text.

    Hope you can help

    Thanks
    Micky

    1. Hi!

      You can create one more rule for column N with no formatting, and move it to the top of the rules' list:

      =OR($P2="Y", $P2="N", $P2="No answer")

  41. How can I enter the date with something like a TODAY function, but have the result fixed as it was on the day of entry, instead of volatile result that changes each time I open or refresh file?

    1. Hi Brook,

      There is no function in Excel that would let you enter the current date as a fixed unchangeable time stamp. You will need a VBA macro for this or you can use the shortcut Ctrl+;

      1. If your goal is to get a static timestamp in your Excel sheet, then use one of the following shortcuts:

        Ctrl + ; to insert the today's date in a cell.
        Ctrl + Shift + ; to show the current time.
        To display today's date and current time, please see Shortcuts to sho

  42. Hi,

    I need to know the formula to use if I want to get the earliest date from a row of dates. Example, I have 4 different dates, ( May 1, 2016 ; Sept. 2, 2015 ; August 19, 2018 and Sept 8, 2018). I need to get the earliest date from these 4 dates and it should show up. What formula do I need to use. Thanks.

    1. Hi Florence,

      You can use the MIN function, for example:
      =MIN(A1:D1)

      Where A1 to D1 are the cells containing the dates.

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)