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

  1. What is the formula to put two strings together. I need =DATEDIF(A1,A2,"M") but if the A2 is blank calculate by "today" =DATEDIF(A1,"TODAY(),"M")

    1. Hello Bonnie!
      The formula below will do the trick for you:

      =IF(A2="",DATEDIF(A1,TODAY(),"m"), DATEDIF(A1,A2,"m"))

      I hope it’ll be helpful.

      1. hi sir

        if 07-Aug-19 : 26 -Feb-2020 =DATEDIF(07-Aug-19,26 -Feb-2020,"d") =569days and the next month

        7-Sep-20219: 26-Feb-2020 =DATEDIF(07-Sep-19,26-Feb-2020,"d") =538days

        how to formulate the total days in 19months" 07-Aug-19, to 26 -Feb-2020 =5,592

        1. Hi,
          What do you want to calculate exactly? Your question is not entirely clear, please specify.
          My guess is the date should be 26 -Feb-2021.
          Explain which 19 months you are talking about: " total days in 19months” 07-Aug-19, to 26 -Feb-2020 =5,592"? It's 203 days.

  2. Hi,
    How do I calculate the days completed based on the ()Todays (current date) from a start date and an end date, please?
    Example:
    Start Date: 20/04/2020
    End Date: 10/05/2020
    Today's Date: 26/04/2020
    Numbers of days completed:?
    What's the formula to calculate the number of days completed, taking into account the end date?

    1. Hello Emmanuel!
      If I understand your task correctly, please try the following formula:

      =DATEDIF(A1,TODAY(),"d")

      where A1 - Start date.
      You can learn more about DATEDIF in this article on our blog.
      Hope you’ll find this information helpful.

  3. WHAT IS THE FORMULA TO HAVE A DATE CHANGE COLOR (YELLOW) 30 DAYS PRIOR TO THE DATE SHOWN AND CHANGE COLOR (RED) AFTER THE DATE SHOWN

  4. Could I use =DATE ( for copying another date?

  5. I'm trying to concatenate 2 dates (arrival and departure) so that the result looks like this: Feb 2 - Feb 5 or, if there's a month boundary: OCT 28 - NOV 20

    I can't get a formula to work using DATE or TEXT, etc. For example:
    =IF(TEXT(D2,"mmm")),TEXT(E2,"mmm"))),CONCAT(CONCAT(TEXT(D2,"mmm","/",TEXT(D2,"dd"))....

    There are no helpful error messages.
    Any ideas?

    Thanks.

  6. Is there a formula or an option that will restrict Now() and Today() function to update automatically? I want them to stay fixed from the day I select "yes" on the cell.

    This are my current function commands:
    =IF(I4="","",IF(I4="yes",TODAY(),"Check SP"))
    =IF(I5="","",IF(I5="yes",NOW(),"Check SP"))

  7. I want list of formulas to change the date range 01/01/2020 - 01/31/2020 with formula.

    If in between I 'm changing the date then it should continue from the date i changed.

    Thanks,
    Mustakeem Qureshi

  8. Hi all,
    I need formula that will count only number of days that have passed - 1 day, for each month.
    =DATEDIF(A2, TODAY(), "d") this formula counts number of days that have passed since specific date, I need end that also.

    Which means the final number for January should be 31, for February 28(29), for March 31.

    Thank you

  9. Hi,

    How do I add a leap year into an excel formula. I have one set for the Julian calendar which works off a number per day of the year for each of the 365 days. However, I cannot get it to figure out leap years. The formula I am using at the minute is: =IF(C2="","",DATE(YEAR(TODAY()),1,C5)). C2 is where we put the code and C5 is the date.

    Thanks

    1. Hi Matthew,
      The same formula you sent will work in leap year too. It will simply consider February 29th as the 60th day of the year.

      If however, you need to check if the year is leap or not, here is the formula for you:
      =IF(MOD(YEAR(A1), 4), "normal year", "leap year")

      Where A1 is the cell with a date.

  10. I am trying to find the baseline percentage of training hours that an employe should be at on the current day. So if an employee has 3000 minutes worth of training to do I would like to have a cell that tells me the percentage that the should have completed on that day.

  11. I have a field like "Thursday, 11/7/2019" how to extract only the date without the day of the week.

    Thank you,

  12. i try to find the remaining day, i try all formula but showing only "VALUE" command only
    what i want to do..?

  13. I need to calculate prorated days for real estate closings automatically for the tax prorations. I have everything figured out except I have to manually enter the prorated date. For example, house closes june 1st, it will always calculate days until june 30th. I simply have a formula subtracting june 30th from june 1st to give me number of days, however, i have to constantly monitor the 6/30 date to make sure the year is the following june 30th, I'd like to automate this. how do i enter a formula that says I want this cell to say 6/30/(after todays date)? so, if today is 8/23/19 I want the prorated date to read 6/30/2020. If it were say, 4/30/19, I want the prorated date to read 6/30/19, so always the june 30th after whatever date.

  14. Hi,

    If I use the formula for today's date, will the date update every day?
    I'm looking for a formula to log the current date when a certain value is reached, but if the TODAY formula updates to current day I won't be able to log the date the value is reached.
    Can someone please clarify how this works? And if it does only give the current date, can you please let me know if there is a formula to log the current date and not update daily?

    Thanks,
    Tyler

    1. =IF(B9>0, TODAY(), "" )

      8 | A | B |
      9 | 12/14/2019 | Reachable value |
      10 | | If Reachable is Null then A-10 show is empty |

    2. Hi Tyler,

      Yes, the TODAY formula updates automatically to always show the current date.

      If you are looking to insert today's date as an unchangeable time stamp, this can be done with the Ctrl + ; shortcut or a more complex formula that uses a circular reference. You can find full details in How to insert today date & current time as unchangeable time stamp. However, using circular references in Excel is always a risk, so please be sure to weigh all pros and cons carefully before using that formula in your worksheets.

    3. not sure what you are asking, current date is not current if it doesn't update

  15. start date and end date is greater than 6 months then count full year.
    for example
    01-01-2000 to 02-04-2019 the answer is 28 year 6 months and 1 day
    but i get the only 29 year only
    01-01-2000 to 01-04-2019 the answer is 28 year 5 months and 30 days
    but i get the only 28 year only
    any formula in excel

    1. try it
      =DATEDIF(B1,B2,"y")&" Years " & DATEDIF(B1,B2,"ym")&" months " & DATEDIF(B1,B2,"md")&" days "

      1. Hi .. i have problem , how to make month and year only to combine, and otomatis.
        example :

        the label show only "2212" how to make this formula
        thank you

    2. try to this type of formula you will get

  16. I need to make daily sign-in sheets for company visitors. Is there any way to make one sign-in sheet and have the working days populate for the rest of the month?

    1. I can help you out for your query.But tell me one thing that you said "1 sign-in-sheet and have the working days populate for the rest of the month". Does this mean you want to calculate the present days for the visitors or the remaining days of that particular month?

      1. Not OP, but it would be great to calculate the present days for the visitors up to a certain date. For example, a sign-in-sheet that begins at a certain date, counts up to, and then ends after a period of time like 3 months.

  17. How to get the number of remaining days for a specific date, eg- if A1=29 I need 2 as a return in B1 if today's date is 27, same way if A1=3 I need 3 as a return in B1 if today is the last day of month.
    So please suggest any formula for this, if there is any.

  18. what is formula for date.
    turn color or highlight when future date become current date.
    for example if date enter in cell 15-02-2025 and when computer date become 15-02-2025 it highlighted or color turn

  19. I want to be able to click and drag a formula to add 7 days like as follows

    ABC 01/14/2019 MIC 01/14/2019 XYZ 01/14/2019 ABC 01/21/2019 MIC 01/21/2019 XYZ 01/21/2019 .....

    None of the formulas above seem to allow something like this, my sheet needs 4 columns for each date, and then the next 4 columns to be 7 days after the previous 4 columns, but each has the 3 digit prefix for the date.

  20. Hi svetlana
    i have a excel problem can you help me
    below mention table include some employee numbers and their "IN & OUT" in all one row ..how can i get this "IN & OUT" to two rows with according to employee numbers
    Emp no and In And Out
    4 01/12/2018 7:49
    6 01/12/2018 17:02
    1 01/12/2018 17:03
    4 01/12/2018 17:03
    9 01/12/2018 17:03

    52 03/12/2018 8:03
    26 03/12/2018 8:03
    6 03/12/2018 8:03
    9 03/12/2018 8:06
    2 03/12/2018 8:11
    1 03/12/2018 8:32
    40 03/12/2018 9:13
    4 03/12/2018 9:31
    1 03/12/2018 17:56
    52 03/12/2018 17:59
    40 03/12/2018 17:59
    6 03/12/2018 17:59
    26 03/12/2018 17:59
    9 03/12/2018 17:59
    4 03/12/2018 17:59
    2 03/12/2018 18:00
    52 04/12/2018 8:00
    26 04/12/2018 8:00
    9 04/12/2018 8:01
    1 04/12/2018 8:03
    2 04/12/2018 8:05
    6 04/12/2018 8:06
    40 04/12/2018 8:56

  21. Hi Svetlana,

    I am working on Task Manager for 2019 and facing issue while retrieving the weekend date for current week (whichever it may be) excluding holidays and weekends.

    I am using below formula:
    =WORKDAY.INTL($L$1,NETWORKDAYS.INTL($L$1,$R$1,1,$Y$4:$Y$14),1,$Y$4:$Y$14)

    $L$1- current date
    $R$1- weekend date
    $Y$4:$Y$14 - holiday list

    It is working for normal week but fails whenever there is hoilday.

    Thanks in advance.

  22. Hi,
    I want to validate a cell with current date. if it is less than current date then the color of the cell should red or if it is equal to current date then the color should be green.

    Regards,

    Bibhu

  23. Hello, please let me know if a return of "year.month.day" is possible. So the column A would be:
    2018.01.01
    2018.02.24
    2018.03.13
    Etc,

    Thanks.

    1. Hello!

      If column A already contains dates, you can simply set this custom format for them: yyyy.mm.dd

      For this, select the dates, press Ctrl+1, on the Number tab select Custom in the Category list, and type the above code in the Type box. For more information, please see How to create a custom date format in Excel.

  24. Ineed the persian date(1397)in excel i tried alot but exel only has two date ie gregorean and hijri qamari date.the microsoft must instaled the persian date too.so now how can i solve my problem.

  25. How to mention a date in salary sleep, if employee is absent on specific date?

  26. Hello,
    I kind of need some help. I have make a table containing dates which are deadlines. A side there is also a final deadline for that entire table. What I want to do, if any of the date changes the final deadline date should automatically change for the number of days which changed on that one date. Is it possible to accomplish that in excel and if it is, how can I do it? :)

    Thank you in advance. :)

  27. I have a column that calculates a projected date based on another column that explains the number of days that either needs to be added or subtracted and then posted as a projected and specific date so we can post to a calendar as an alert. However, what I REALLY need is a formula that shows the previous FRIDAY if or when it falls on a weekend or holiday.

    HELP and THANKS!

    -steve

  28. Hi,

    Can i get an exact date from this only "Sept-2018"

    I am creating a table where I just need to replace the specified cells details then change the date on one column.

    Thanks ahead!

    1. I think not possible

  29. Hello,

    I am trying to modify a spreadsheet to highlight the dates I have to make a status call. I work at a financial firm, every time we process transfer paperwork, we have to call the contra firm 8 business days after mailing it, then every 5 business days after that. I would like excel to not only automatically generate these dates in at least three different columns, but I want it to automatically highlight the dates once they've approached. Are you able to provide a formula for this?

  30. DATE 01.01.18 AFTER 30 DAYS DATE WE NEED AND DATE IS THIS FORMAT ONLY ( EX:31.01.18 ). PLEASE SEND THE EXCEL FORMULA

  31. HOW TO REFERENCE CELL TO AUTOMATICALLY CREATED DATE AND TIME THIS NOT CHANGE TODAY DATE ONLY CELL ENTRY DATE AND TIME.

  32. How to get required data in pivot without filter option instead of using condition within pivot?
    If I will update the data further in the table, the pivot will show the data with accept the condition.

  33. Hello,
    I have two sheets in excel 2007, where I enter via barcode scanner serial numbers of devices, one sheet is for direct sales and the other sheet is for credit sales.
    I need a way to validate for duplicate s/n between two sheets. At this moment I am using the formula =COUNTIF(sheet1!d5:sj30000), like conditional formatting highlighting the duplicated cell. The system find duplicates very good, but becomes very, very slow.
    Now I want to validate at specific time, for example in the night, when the system is not used.
    Can you help me to setup time driven validation formula to trigger at specific time.
    Thank you very much for your time.

  34. I am trying to get an IF formula to recognize a date entry so if the cell G9 contains a date, then return the value in cell F6 e.g. If(G9="date",$F$6) - however my formula is returning a result of FALSE even though there is a date in G9 which is 11/07/2016 in the format of dd/mm/yyyy. Any help appreciated, I have tried everything instead of "date" in the formula, I have used "dd/mm/yyyy" "number" "datevalue" but nothing is returning back the value in F6, I'm either getting FALSE or errors of #NAME? or #VALUE?

    1. Dear MC,
      Change the formula to
      =If(G9>10000,$F$6) or
      =If(isnumber(G9),$f$6,"False")

      In Excel date is a number.
      1 Jan 1900 = 1;
      2 Jan 1900 = 2 and so on.
      11 July 2016 = 42562, regardless of which number format you use.

      I hope this solves your problem.

      Regards,

      Vijaykumar Shetye,
      Panaji, Goa, India

  35. Am puting date on letter 13 May 2018.and i want on other page date is automatically added by 2 days i.e. 15 May 2018 base on date i put on letter.

  36. what does mean this formula ?

    =w(B15,B16,0)

    what does mean =W ?

    Thanks

    1. Tarek:
      I would say the "W" is a typo. I'm not aware of an Excel function "W". Either that or maybe it is a user defined macro.

  37. Good day

    I would like to hard code the 26th of March (irrespective of the year) as the start of week 1.

    Date format currently: YYYY/MM/DD

    How do I go about this?

    Thank you!

  38. Hi,

    I need an excel formula to calculate the number of hours that occurs between start and end of a given period, number of hours before and number of hours after that period.
    Example: The period Starts 08 AM and ends at 04 PM

    regards,
    Francisco

  39. Hi,

    I have a dated if statement but only want the true,false value to be considered in the future?

    =IF(TODAY+$I$2()TODAY(),A1<=(TODAY()+days))

  40. If I need to consider only month Feb 18 against 18-Feb-2018 which formula need to used ?

  41. hi since MS in their infinite wisdom decided to remove date picker in office 365 I need answer to cell to have current date in cell but also once saved remain that date and not update to current date when file opened

    TIA

    Lewis

  42. what is wrong in this formula ?
    plz correct it

    COUNTIFS(DATE(YEAR(Y:Y,$AU$7,F:F,AS8)

    while
    Y:Y is a range of different dates
    $AU$7 is a specific year
    F:F is a range of different designations
    AS8 is a specific designation

  43. when in cell a1 have only year and b1 have complete date, how can we subtract them considering the a1 as 01/01/####

  44. HI,

    example
    11/12/2017 - 5/3/2018 HOW MANY DAYS BALANCE COMING

    1. Hi,

      If your task is to calculate the number of days between two dates, I'd recommend you to try out our Date & Time Wizard. To see how the add-in works, you can download and install the fully functional 14-day trial version of Ultimate Suite that contains all our add-ins for Excel (70+). After the installation, you'll find Date & Time Wizard under the Ablebits Tools tab.

      If your task is different, then please describe it in more detail.

  45. Everytime I type in a specific column, I'd like the current date to show up in a specified cell

  46. hi,

    how can i add 90 days (3 months) and 365 days (1 year) in current using date

    example
    23/02/2018 to 22/04/2018
    23/02/2018 to 22/02/2019
    ????????

  47. I am building a sheet where i import data from a daily updated workbook. I put in a formula to calculate the date using =today(). However, I am finding it is changing the other dates in the column to the current date. Is there some other function i can do so it will not change the previous entered dates?

  48. one date in a column on 20 rows. how it show in next sheet only in one row only one date?

  49. I looking for 2 separate date formulas but ultimately produce the same information

    1 formula I need it to return the number of working days in the month on that day without weekends or holidays included ex: 01/15/2018 = day 10. This will refresh at the beginning of every month

    2 same formula but rolling for the whole year. excluding weekends and holidays.

    Thanks

  50. Hi Svetlana

    If I have date in one cell, how can I have day in next cell which is corresponding with this date

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