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 10. Total comments: 595

  1. I am creating new dates based on a forecast date: Example a forecast date of 12/01/2017... to get me new date I use =EOMONTH(H17,0)+30, which this would create 1/30/2018. But that is a problem because I cant have dates go into the next year. It would have to stop at 12/31/17... how can I do that in excel?

  2. Can any one help me to create formula in Excel sheet for calculating tax 5% on or before 14/9/2016 and 6% from 15/9/2016 with the help of IF Statement.
    _________________________________________________
    A | B | C | D |
    _________|____________|_____________|____________|
    Date Amount Tax @5% Tax @6%
    5/9/2016 5000 ? Formula ? Formula
    14/9/2016 25000
    15/9/2016 27400
    25/9/2016 125000
    5/7/2016 215600
    5/4/2016 15000
    25/10/2017 2000

    1. Hello, Nitin,

      If I understand your task correctly, please put the formula below to cell C2:
      =IF(A2<="14/9/2016", B2*5%,"")

      and the next one to D2:
      =IF(A2>"14/9/2016", B2*6%,"")

      you need to copy both formulas down the columns. If you don't know how to do that, please check out this article.

      Hope it will help!

      1. To Natalia
        thanks you have solved my big problem.

  3. Hi There! Can you help me with this calculating the days from dates please? I want to add say 3/16/2017+3/20/2017 and see the result at 4.

    Thanks for your help!

    Cory

    1. Hi, Cory,

      if A1 contains 3/16/2017 and A2 - 3/20/2017, the following formula will return 4:
      =$A$2-$A$1

      Or you can use the other one:
      =DATEVALUE("3/20/2017") - DATEVALUE("3/16/2017")

      Hope this helps!

  4. Hi,

    Can anybody help me in this?

    I have the list of date, I need take out Previous date and Next date based on current data and it should update automatically whenever I open the excel.

    Regards,
    Ajit kumar

  5. Hi

    Would you advice me how to make a cell not to display any result if calculating a date (date of birth).

    What is actually happening is...I'm calculating a date of birth...but when I delete that date it automatically display it's own value e.g 117...the formula I've used is =dateif

    Thanx

    Jerry

  6. Dear Sir/Mam

    Mail Excel Me Ak Aisa Farmula Chahta Hun Ki Agar Meri Vehicle Ka Time 50 Hr Fix Hai 2500 Km Ke Liye To Agar Wo Vehicle Late Ho Rahi Hai To Wo Red Colore Ho Jaye Agar Wo Vehicle Time Pe Pahuchi Hai To Green Agar Wo Time Pe Pahuchi Hai To Yelow Ho Jaye Agar Kisi Ke Pas Aisa Farmula Hai To Please Let Me Us Now In Urgent.

    Thank's
    Prabhat

  7. I have a column of expiry dates. I need to display those dates that are going to expiry in the next two to three weeks.

    thanks in advance.

    1. Hello, Santa,

      you need to use Conditional formatting here.
      If the dates are in column A, you create a rule that will be applied to A:A with the following condition:
      =(A1+14)>TODAY()

      Please use the link above to learn how to create and use conditional formatting in Excel.

  8. Hey there,

    I am trying to figure out a formula in which I have a date in one column and the column next to it has to show a specific date for example A1 has a date ex today's date and B1 needs to show a date of 9/10/2018

  9. Hi. I need to a formula for one cell in a column that will return the date for every Sunday from here on out. The date format i would like to use is 17-09-03. I would drag the cell containing this formula down the column to fill in the blanks on the spreadsheet. It will fill in this coming Sunday as 17-09-10 then continue into next year depending on how far I drag the cell each time. Right now I just manually enter the dates in the format mentioned. Any ideas please? Thanks

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

  11. Can you use the weeknum formula with the =today() formula so that every time you open the sheet it updates the date and automatically gives you the current week number?

  12. Hello Everyone...

    Please some one can help me on this issue...

    I have total days which has been calculated due dates between start date To end date..

    now i want to know the formula for those days can be convert into datewise..

    example - the start date is 01-Sep-2017 to end date 30-Sep-2017 so total days are here 30 days.

    now i want the date format to appear in excel cell as last day of (30) day..

  13. Hello - I am trying to have one column populate a date, if another cell has 100% in it. Can you help me with this, please? An example of this would be, I need 30 of this particular item and I finally get my 30, I have a column that populates 100%, but I would also like a column to then populate the date I hit 100%. Make sense?

  14. Hi
    i imported webdata and made a template in excel, but i have an issue i want it to refresh only in fist day of the month not only that i want it to store the average of each day information in a new sheet.
    Thank you in advance
    Alexander Michael Gkourtziev

  15. Hi
    I am looking for a formula to inform me what is the excact day every 25th of the month every time i open the worksheet.
    Thank you in advance
    Alexander Michael Gkourtziev

  16. Hi,

    What formula would I use if I want the excel sheet to count a drop down menu item for the current week.

    Like lets say this is my excel sheet:

    1 A B C ..K
    2 7/17/17 Requests Can you fix my chair? Maintenance
    3 7/18/17 Maintenance Fixed chair Low
    4 7/21/17 Low Mopped Floor High
    5 7/21/17 Requests Can I get my box fixed? Med
    6 7/23/17 High Put Insulation in Requests
    I want the excel sheet to count every time the sheet says requests and it occurred this week. I want it to update after every seven days.

    I tried

    COUNTIF(TODAY()+7, $K6)

    $K6= Requests (data validated for drop down menu items for column B:B)

    But it didn't work

    Thanks,

    Leilani

  17. I will enter data in every 10 days.I want to set upcoming specified date after no of day.if i enter data every day then this formula can be work=TODAY()+B1 where B1 is Project duration.

    For an example:- Today date is 04/07/2017, data enter date also same 04/07/2017.

    date Project duration in day
    28/06/2017 17
    30/06/2017 5
    01/07/2017 3
    04/07/2017 2

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

  19. Would like to have day-of-week display as full text.

    Cell A1 has date. Cell B1 contains =DAY() which displays the numerical day of week. Would like to have Cell B1 display the text version of the day, eg. Monday, Tuesday, etc.

  20. Hello,

    I'm looking for a formula to calculate status in milestone table. In the table, there are lists of activities (rows) and 5 Milestones (columns across, M1-M5). I want the table to show the next milestone date to determine the status of each activity. Can you show me how?

    thanks in advance,
    Celeste

  21. Allocation Start Date Allocation End Date
    1-Oct-16 31-Mar-18

    Hi,

    I want May month networking days from the above mentioned allocation start date to allocation end date. please help me, i need to get the networking days for entire column.

    Thanks

  22. Hi,

    I am looking for a formula for crew joining vessel.

    Example:
    Crew joining date on 1/4/2017

    I wouldn't know the crew signing off date until given and at the same time I want to know how many days onboard as I need to monitor it on daily basis how many days the crew is onboard until his actual signing off date.

    I would like to have Cell A (indicate Joining Date), Cell B (indicate his Signing Off date, which will be leave blank until date is confirmed), Cell C (indicate the number of days onboard as to date)

    Thank you.

  23. 1st May To 5th May
    How can I show this using excel formulas.
    This will continue for whole month like below
    1st May to 25th May
    1st May to 26th may

  24. I am looking to have the date change to the next week after a payment is made.

    Ex: payment is due on 5th. On the 12th, the next payment is due. Once the payment is put in, I want the date to change to the next week.

    Is that possible?

  25. I have a report that shows each month separate that throughout the month needs to have the current date, but the date needs to stop at the end of the month. (ex: current date is 4-10, on 5-3 the date needs to stop at 4-30 in that column) Is there an "end of month" stop date formula?

  26. Hi I'm making a template to keep a timetable and I would like formula to have the correct day of the week appear in cell B when the date is entered in cell A please.

  27. what would be the formula for following condition:
    interest would be 15% for dates jan to dec 2015 and 18% for jan 16 onwards

  28. what would be the formula for the following condition:
    interest rate would be 15% for date jan 2014 to Dec 2014 & 18% after that

  29. 5 years gets full salary

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

  31. Dear Sir/Madam,

    My start run 01-jan-2016 that time km was 2000 km average per day is (15 km)
    My question is at what time i will covered my km 10000 and in which date.

    With Regards,
    Kapil Sharma
    7290849754

  32. Hi,

    I have a list of duplicated project names with different timeframes, which I want to reduce to see the actual timeframe/phase - the one which MATCH TODAY's date best.

    Due to this I removed the duplicates and now I need a formula that can choose the timeframe which MATCH TODAY's date best.

    To make it more clear I already created a formula for the finish date, that finds the latest/maximum date, but not the BEST MATCHING TIMEFRAME TO TODAY's DATE...

    Example:
    Row A = Project names to be searched in(Duplicates incl.)
    Row B = Project finish date
    Row C = Project names (Duplicates removed)

    MAX(IF(($A$1:$A$100=C1),(B2:B100)))
    - It is an array formula, so press Ctrl, Shift + ENTER

    Hope my question makes sense.

    Best regards,

    Niklas

  33. Hiii..

    I have list of 200 cases with their details and next dates. i want only those cases to be displayed which are on tomorrow only. i dont want other cases to be shown with their details. if there are only 2 cases on tomorrow then it will show me only those 2 cases which are on tomorrow. i manualy inputs the dates.

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

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

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

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

  38. Can you please help with below points :

    I want to use Condition like if particular cell date is Today then result will be (this) if cell date is Tomorrow then Result will be (this) or else result will be (this).

    What will be the Formula for the same...

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

  40. I need help.
    im working with conditional formating. I have this condition; when it's true the cell goes red =BA5>=DATE(2017;1;31). Everything ok. But i have already typed this date (31.1.2017) in BA5 cell, couse it's the end of the course (for my record). I would like that excel is counting days from the date i set this condition till the date that is written in cell (in my case 31.1.2017). So, if today is 26.1.2017, excel is coloring cell BA5 green. And it does that for another 4 days. 5th day its 31.1.2017 and it colors cell red.
    I believe i have to include =today() function, but dont know how. I have MS Office Pro Plus 2013.

    Thank you for your help

  41. Thank you, very helpful.

  42. Hi,
    Can you please help me in creating moving vertical line with respect to week/date.

  43. Hi,
    Anyone can help.
    Is there any way to calculate the nearest coupon date from today( today is included) in excel given the maturity date, coupon times per year.

  44. Hi

    I would like to calculate difference between today() and the earlier date by giving condition like if status='Closed', then it should not calculate the difference, if status='Pending', then only it should display the date difference.

    regards,

    Raviprasad.

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

  46. ok I have a column of dates and would like to count or total by year only. Say there 1000 dates I would like to know how many time 2013 shows up, out of the 1000, and how many times 2014 does so I can make a table. the format is y/m/d in the columns. The range of years is 2004 to 2030.

  47. Is there an Excel function that will roll out (down a column or across a row) all the dates -in order - of a given month and year?

  48. Hello Svetlana Cheusheva,

    I need a help. Actually I am a admin of a bio-metric device. There are 3 shifts are going in my office.
    In time Out time
    1. 6AM- 2PM
    2. 2PM- 10PM
    3. 10PM- 6AM

    but the 3rd shift time intime 10PM is taking as
    In time Out Time
    day1 out time 10PM
    day2 in time 6AM

    How can I make it for same date 10PM as intime and 6AM as out time in excel?

  49. Hi I am working on a "Charge, Credit, Balance worksheet"
    Example: I have a tenant that in a year period has only paid 8 rent payments out of 12.I want to apply the 8 payments to the oldest balance.

    Date charge Credit Balance
    January 500 600 1827
    Dec 500 0
    Nov 500 287
    Oct 500 0
    Sep 357 0
    Aug 357 0 0

    I want to get a break down as if I apply Nov Payment to August charge, what would I be charging for Augs(I am aware that 357-287=70) so 70 will be apply to sep's charge 357+70=427-January's payment of 600. 427-600=(173)
    the 173 will be apply to Otb's charge which will let a charge of 327 for Oct instead of the 500.so if I was to give a notice to pay, It would look as follow:

    January 500
    Dec 500
    Nov 500
    Oct 327

    Total to collect 1827.

    I do it by hand, but I would love to know the formula to save me some time.

    I don't know if I made sense but, if I did can you please help me? I will greatly appreciate it. Thank you in advance.

    Jess

  50. I have a worksheet I am building for a friend & they want to be able to enter an event start & end date for the event. Once these dates are entered, I need a formula that will determine if the event occurs during either of the two stormy periods where they live.

    I have been trying to just combine an if statement with an AND statement, which works just great, except I have to provide a year for the statement to work correctly. My problem is that I need the formula to work no matter what year the event occurs in. Is there a way to either get Excel to ignore the year or for me just to include a YEAR((today)) tag in my dates which delineate the stormy season?

    The formula I am using is as follows:

    =IF(AND(G28>=J25,G29<=K25),"Chance of Storms","Clear Weather")

    where
    G28 & G29 = the start & end dates for the event
    J25 & K25 = the start & end date of the normally stormy season (June 17th through July 29th).

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