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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  30. Hi Svetlana

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

  31. HI
    want update every day automatically pending days (E.g one project started date available project not completed i want to know no of day's pending without inserting end date)

  32. Hi All,

    Please help me for this. I have 3 different wordings in one column like TYPED, SEND & RECEIVED. Now I need to put date on next column like when I typed "TYPED" word on that column then in next column it need to show the current date, but wont change in next day when i open the excell on next day.

    Like this when I put next word (i.e) "SEND" the current date has to be displayed and also wont change in next day when I open excell in next day.

    If anyone can help me in this please help me.

    Thanks,
    Faizal

    1. Hello,

      I'm afraid there's no easy way to solve your task with a formula. Using a VBA macro would be the best option here.

      However, since we do not cover the programming area (VBA-related questions), I can advice you to try and look for the solution in VBA sections on mrexcel.com or excelforum.com.

      Sorry I can't assist you better.

  33. please help me
    i want to calculate number list in one number.
    i want like this
    {1+5+8+16+7+26=63>>> but i want 6+3=='9'}
    {19+15+32+16+167+366=615>>> but i want 6+1+5=='3'}
    {451+456+268+176+9+256=1616>>> but i want 1+6+1+6=='5'}
    i want '9','3'&'5' number count formula in excel.

    1. Dear Sudesh,
      Use the formula
      =SUM(IFERROR(VALUE(IF(COLUMN(A1:J1)=COLUMN(A1:J1),MID(SUM(IFERROR(VALUE(IF(COLUMN(A1:J1)=COLUMN(A1:J1),MID(A2,COLUMN(A1:J1),1),0)),0)),COLUMN(A1:J1),1),0)),0))

      Formula will provide the sum of the digits like 123456 as 3.

      If you also want to get the first stage of the sum of digits, then you can use the following formula.
      =SUM(IFERROR(VALUE(IF(COLUMN(A1:J1)=COLUMN(A1:J1),MID(A2,COLUMN(A1:J1),1),0)),0))

      This will give result of 123456 as 21.

      Note:
      1. Change the cell references as required.
      2. Presently number is in cell A2.
      3. Formula is to be entered as Array Formula, using Control+Shift+Enter, instead of Enter.

      Regards,

      Vijaykumar Shetye,
      Panaji, Goa, India

    2. Hello,

      I'm afraid there's no easy way to solve your task with a formula. Using a VBA macro would be the best option here.

      However, since we do not cover the programming area (VBA-related questions), I can advice you to try and look for the solution in VBA sections on mrexcel.com or excelforum.com.

      Sorry I can't assist you better.

  34. I have a spreadsheet that I sometimes keep open for several days. I have the TODAY() function in one cell. There are many other cells that refer to this for date calculations.

    Without saving/reopening, is there a way to trigger an update automatically after midnight? Executing the cell the next day doesn't update.

    1. Hello,

      I'm afraid there's no easy way to solve your task with a formula. Using a VBA macro would be the best option here.

      However, since we do not cover the programming area (VBA-related questions), I can advice you to try and look for the solution in VBA sections on mrexcel.com or excelforum.com.

      Sorry I can't assist you better.

  35. I want to know how I can use the Duration Days and Start Date to get the End Date.

    Example

    Duration Days Start Date End Date
    24 11/30/17

    1. Hello,

      Please try the following formula:

      =TEXT(DATEVALUE("11/30/17")+24,"mm/dd/yy")

      Hope it will help you.

  36. Hi,

    I would like to change a date automatically to today's date once data is changed in another column.
    e.g Column A is a Serial Number, Column B is the software Version, Column C is whether Serial Number has updated Software Version = 0 for no and 1 for yes. Column D is the date the software was updated. I would like to know what formula to use when we change the software version ... column D should automatically update to today's date.

    If this is unclear, i will send the spreadsheet for clarity.

    1. Hello, Maryka,

      I'm afraid there's no easy way to solve your task with a formula. Using a VBA macro would be the best option here.

      However, since we do not cover the programming area (VBA-related questions), I can advice you to try and look for the solution in VBA sections on mrexcel.com or excelforum.com.

      Sorry I can't assist you better.

  37. I have a problem
    In sheet1 i have a table which is
    A B C D E F
    _____________________________________________________
    1 Date Qnty
    2 29/07/2017 130
    3 07/08/2017 300
    4 07/08/2017 220
    5 10/08/2017 140
    6 21/08/2017 50
    7 07/09/2017 100

    I want to put data which is on 06/08/2017 i.e data required nearer to 06/08/2017 (it's 07/08/2017)in cell E2
    How can i do
    please guide

    1. Hello, dibya,
      I'm afraid there's no easy way to solve your task with a formula. Using a VBA macro would be the best option here.
      However, since we do not cover the programming area (VBA-related questions), I can advice you to try and look for the solution in VBA sections on mrexcel.com or excelforum.com.

      Sorry I can't assist you better.

  38. my scenario is I have to calculate the first 6 months and extract the data ,then compare the column of which determines the period where one year is equivalent to R20 and the maximum is 5 years, after the period of which determined by the amount paid .then after the expirity theres 3 months period again then data is extracted t another table please help me out I really need help
    ,

    1. Hello, sfiso,
      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Please also don't forget to include the link to this comment into your email.
      I'll look into your task and try to help.

  39. I want to create conditional formatting in one cell based on the date input of another cell. However, I do not know the value of the date. Is there a formula that says "whatever the date value entered in the cell + 1" for example? I am trying to calculate when something goes past due, but I do have an assigned date yet.

    Thanks in advance

    1. I meant that I DON'T have an assigned date yet.

  40. Hi Team,

    I would like to know , how to populate dates in this format, for example 08/01/2017 - 08/07/2017 and if I drag it i should get the next week's date like 08/08/2017 - 08/14/2017. Any help ??

    Thanks & Regards,

    Vam...

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

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

  43. 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!

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

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

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

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

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

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

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

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