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

  1. Hi,

    I have a spreadsheet that has a record of all our stock. There is a column that indicates how many days old each item of stock is. However at the moment I have to manually change the age so for example if it was 2 days old yesterday I would have to change it to 3 today then 4 tomorrow etc. What I would like to know is if there is a formula that you can get this information to update automatically each day so that it just ticks over the older the stock becomes. Thanks

  2. I would like to know how we can retrieve the date for 3rd sat of every month for the year 2015

  3. Dear Madam/Ser,

    Which calculationcould I use for:
    I have a production of some product of 34 h, and other product 6 h
    The working process is in two shift, output/shift is 12 h.
    I would like to enter the start time for product1, the calculation to calculate the end date of production of product1, excluding the weekends and non woring time (from 22:00 to 06:00 next day). The production of product2, start at the end time of product1 and calculate on sam principle the end time of production for product2.

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

  5. Dear Madam

    Please Check I Will Send The Data To Your Emil Id , Which Your Provided ID
    My Personal Mail Id Was (MukundhaRaorm13@gmail.com)

    Please Check Madam

  6. 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. Dear Mukundha,
      It is difficult to provide a solution without seeing the data. Could you please send a test worksheet with some records you want to use for the calculation to support@ablebits.com and describe the expected result?
      We'll do our best to assist you.

      1. Ok Madam

  7. hi
    thanks!!!

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

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

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

  11. I want excel to fill on completion date if I change status to complete. What formula should I use?

  12. hi

    i want to create upcoming date auto change by formulas
    for example today 10-12-2015 so next column next five year auto add than resulted change from 09-12-2020

    so plz help what is the formulas set with excel..

  13. In ticketing systems we have three type of ticket for different SLA (Service level agreement),deadline for each type of ticket is different related to agreement between Customer and vendor. I want to calculate workday between two date with excluding weekends and holidays, some ticket have suspend status. for instance they become park or suspend and after a while they become active,in this situation I want to exclude suspend period and + weekends and holidays and calculate workday as our deadline,
    I was wondering that what your suggestion is

  14. Hi,

    I have a list of dates from: January 2013 to december 2016
    I want to call the current date of these list accordingly the current month date.

    e.g Now I would need to call from this list date: 20.11.15 since the next date 18.12.2015 is not present yet.

    Cn you help me how to add a formula for this?

    thanks in advance!

  15. Hi,

    I failed to calculate via Index function the "Min" of dates array;

    =INDEX(MIN('Dates'!N15:N35),MATCH(A4,'Students'!B15:B35,0))

    Result is #N/A, though values are available in the date type and both respective cells.

    Can you please help? Thanks.

    Regards,
    Tariq

  16. Hi,

    Want to call the date based on leave applied in From Date and To Date column to know the exact date which he applied the leave for.

    Employee IDs - A1:001, A2: 002, A3: 003....
    Date- B1:Jan 1, C1:Jan 2, D1:Jan 3 ..... till 31st Jan

    Emp ID
    1-Jan 2-Jan 3-Jan 4-Jan From Date To Date
    Casual Leave(CL)
    001 CL p p p 1-Jan 1-Jan
    002
    003
    004
    005

    Appreciate your quick help.

    Regards,

    Laxman

  17. Hi,

    Im trying to use If(a2>0,Today(),"") but trying to make it so that I may update it when I update the A2 cell. how can I do that?

    Killian,

  18. I need a function that will return a value only if the specified day of the month is within a week ending column.
    For example, I have a weekly cash flow forecast and I always pay my utility bill of $500 on the 15th of the month. I want a formula that will only return $500 when my week ending column includes the 15th of the month.
    Please can you let me know if there is a formula I can use.
    Thanks

  19. I have Grade in excel needs to add next schedule date
    for example
    Grade A - add 180 days to the previous date (10/11/2015 + 180 days)
    Grade B - add 365 days to the Previous date (10/10/2014 + 365 days)

  20. Hi,
    Is there a formula for counting workday from start date to end date, where end date =today() until an exact date is entered. Thanks.

  21. Hi,
    I want to create a employee attendance sheet with below details,
    1. Set weekend by our choice any day of week like (Fri and Sat) instead of Sat and Sun
    2. Holiday
    3. auto changeable date if we select month and year

    Please advise accordingly

  22. Is there a formula that will allow me to open an excel file and it first of all create a NOW() DATE and then project a WEEK COMMENCING DATE for 4 weeks later

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

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

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

  26. Hi Meer,

    You can use the NETWORKDAYS function, like =NETWORKDAYS(A2, B2), where A2 is the start date and B2 is the end date.

  27. Hi,
    I am looking for a formula which can give me the count for difference between start date and today's date which excludes weekend. For ex, if i want to see diffenence between 15th Sep 2015 and 21st sep 2015, i should get result as 5 excluding sunday.

    Thanks,
    Meer

  28. I am looking for a formula which can add two years from the reference column date (DD-MM-YY) and and subtract three months from that, show up in next column.

    Is it possible to color a the same output column with two different color based on starting of the date in this column and three months past from that day.

    Thanks,
    Apurve

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

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

  31. Thank you its working now!

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

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

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

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

  36. Hi Yasmeen,

    You simply add 20 days to today's date:
    =TODAY()+20

    If the project duration numbers are stored in other cells, then you can refer to those cells in your formula, for example =TODAY()+B1 where B1 is Project duration.

  37. hi

    I want to know the upcoming specified date after numbers of days, like I want to know of project ending

    project duration 20 days
    project starts today

    so when project will be finished in which date

    thanks
    Yasmeen

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