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

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

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

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

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

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

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

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

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

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

  10. Hi Svetlana

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  49. 5 years gets full salary

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

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