Comments on: Using DATE function in Excel - formula examples to calculate dates

The tutorial explains the syntax and uses of the Excel DATE function and provides formula examples for calculating dates. Continue reading →

Comments page 14. Total comments: 684

  1. HELLO DEAR
    I WANT SHOWN DATE IN EXECL IN BELOW PATTERN
    12/05/2016
    I WRITE 120516 AND ITS SHOW AS 12/05/2016

  2. what formula can I use to calculate the minimum $ value in a colum from todays date forward for every time I open my spread sheet.

  3. You guys are great! looks like you've helped a lot of people. Here is another one for you.

    Im trying to format columns based on an start date and an end date. I've been able to insert a start date and get the following months to populate by using =EOMONTH($A$7,C$1) A7 being start date and row one being by number of months following. As it is now i have add or subtract columns manually to get to the correct end date. Then if i change the beginning date all the cells formatted below are in the wrong months.

    If you can it'd be great to get some help thanks.

  4. I just found your blog and am amazed at the detail and depth of your responses! Quite impressive! I want to create a macro for entering payment data into certain rows of column M (where each row is one day) based on options from a drop down menu (accessed when any cell in column M is right clicked) and based on the date in column A. The drop down menu options are: amount, start date, frequency (1 week, 2 week, 4 week, monthly), and number of payments (max 500). If the entry falls on a weekend, it should be moved back to fall on the Friday before. The first entry will be on the same row as the row of the date in column A and the remaining entries similarly. I know this is fairly complex and thank you so much for your help!

  5. Hi, i am property broker and i want to yellow colour in after 330 days and Red colour after 360 days formula reminder. plz help.

  6. I have a cell downloaded from a web report that contains 13/02/2017 15:30:00 all in one cell.
    How can I extract the date, and then the time into two separate cells further along the page?

  7. Hi, I am trying to use this formula to calculate date difference but I also want the formula to return 0 or do nothing for lines without dates.
    Formula used is =DATEDIF(Q5, TODAY(), "d")/7
    The problem is once I drag down it also fills the lines with blank dates as 6111 which is incorrect but lines with dates are correct.

  8. How do you calculate the number of days from start to end and adding 1 to the results?
    Start : 6/4/2018
    End : 6/6/2018

  9. Enter a formula in cell G5 that calculates the difference between the attendance totals for 2018 and 2017. Copy the formula to the range G6:G11.

  10. Hello,

    I need a date formula for example:

    The two dates are set at 09/01/2016 -10/01/2016 calculating the daily rate at £44.02 but I need it to acknowledge the two dates as two days not one and return the daily rate as £88.04

    Please help...

  11. Hi, I am trying to create a formula that will return a result if the current month is December or if the date range is between Dec 1 - Dec 31 (not specific to year), otherwise return 0.

    I have this so far... but it is dependent on the year being there and being updated every year. How can I make it so that it is any December ?

    =IF(AND(TODAY()>=DATE(2017,12,1),TODAY()<=DATE(2017,12,31)),'DR Schedule B'!F31,"0")

    1. I have found one that works!!! Thanks

  12. I want to enter a birthday, then calculate the age for a specific day.

    Example: Child is born 10/7/10. How old will he/she be on 10/19/17?

  13. Dear,

    I need a easy excel formula see below example

    to 24-11-16 from present 4-1-17 = - due day

    Also need = date-month -year

    Waiting for your answer

    Best Regards

    Russel

  14. i have a problem in excel that is when i am entering 1-1, 1-2, 1-3 ,....... 1-11,1-12 it is converting into 01-jan, 01-feb,...... 01-nov, 01-dec . How to convert them into numbers when i am entering 1-13 it stay in 1-13

    plsss give a detailed solution for my problem

    1. To get the number as you required is to add ' before entering number.
      put '1-1, '1-2 and if you drag you will see

  15. Hi,
    Many thanks for suggesting with the right formulas
    Well i need a formula that particular date data should change if i change the date at the top
    Like In B column dates will come and in the remaining columns some other data. in other sheet if i want some particular "date" data as front page columns here rows and the data should match with the date
    Plssssss help me

  16. Hi there,
    I am trying to input a formula. I have a worksheet with 2 tabs.

    Tab one is the worksheet I input dates and details into.

    Tab 2 has pricing or rates for each day of the year.

    I want my worksheet on tab one to recognise that when I enter a date it populates that rate for the particular day from tab 2

    Can someone assist?

  17. I am working on completing an excel file that acts like a library book checkout/return listing. I was looking to find a way to have Excel see a date in one cell and automatically give a result of a date 60 days later within a different cell. I found the formula below on your site but was wondering how I could have the date automatically pop up when I input a checkout date in a different cell. Sort of like a conditional command. For example: If I place 1/27/2017 in A2, I'd like 3/28/2017 to automatically show up in cell D2. How do I accomplish this command?

    Adding days to a date:
    =DATE(2015, 5, 20) + 15

    The formula adds 15 days to May 20, 2015 and returns June 4, 2015.

  18. I am trying to have a cell show a date 30 days less than the date in another cell. So cell A1 has 03/31/17. I want cell A2 to calculate A1 minus 30 days. Please help!

    1. Never mind. When I actually tried in Excel, super easy. I am trying to find an IF formula that I can use in Smartsheet to get this done. The simple =A1-30 that works in Excel is not working in Smartsheet.

  19. Hello, I am trying to subtract one date from today's date. For example

    A1 = (Review Date) which has a date of 01/27/2017
    Todays Date is 1/25/2017

    But my formula returns -2. It should return 2 without the negative.
    Here is my formula

    =TODAY()-[@[Review Date]]

    I appreciate any help you can provide.

  20. 01/01/2017 14:03:12
    how to single Cell IN EXCEL pls help me

  21. Hello!

    I love your tutorials! I do have a question though.
    I have a documentation sheet that has "Move In" dates and "Move Out" dates for renters in their own separate columns. I also have a column that counts the days from the date that they first move in. For example Move In Date is 12/25/2016 so the current number of days in house according to today's date would equal 25. However the formula I am using currently continues counting from the move in date to the current date (today). How would I get my counter to stop counting based on the move out date and retain the counting ability?

  22. Hello! Could you help me with a formula to add (sum) two periods. For example if I wish to add 01 year, 06 months, 12 days with 02 years, 07 months, 26 days how can I do it? With what formula? The correct answer would be 04 years 02 months and 08 days. But what formula in Excel would give me that answer. Thank you.

  23. my dog show is on 28/1/17
    i want to calculate age from 4 months to 6 months, 7 months to 12 months, 13 months to 18 months, 19 months to 36 months from cut off date which is 28/1/17

  24. Hello,

    I have a start date and I have it so it tells me how many days open, but how would I get that to stop when I put in a date completed?

    Thanks

  25. I would like to calculate 18 months from each of the dates for an entire column?

  26. hi ,
    i would like to make a formula that automatic increase the value every year in April or if i change the cell to month April other cell automatic the value with the percentage increase

  27. Hi,

    How to find out the due date for one who completes the probation period i.e. 6 months. The due date should be in DD/MM/YY. Please help.

  28. AMAZING SITE! ALL ANSWERS ANSWERED SPECIFICALLY.i would like to create a calendar with events, but i would also like the events to be automatically written out in the calendar.
    is there any resources on your site that helps?
    thanks.

  29. Hello, I have used this site many times looking for solutions to my Excel problems. I am trying to figure out a way to add to dates in different columns. I am tracking things weekly beginning on Monday and ending on Sunday. Short of manually adjusting every cell, is there a formula to fix?
    Example:
    C1 is 1/1-1/8
    D1 is 1/9-1/15
    E1 is 1/16-1/22
    F1 is 1/23-1/29
    G1 is 1/30-2/5
    etc. every week to 12/31

  30. Hi,
    how may I convert a given sample/answer (example: 3d 10h 35min) in a certain cell into a total minutes only?

    Thank you.

  31. Hi,

    Need help please!

    I have a date in cell B2. I also have a a table from A5 to B8 (Column A are date values and Column B are price values).

    I need for Excel to use the date in B2 to search for the latest date in Column A before the date in B2, and throw me back the price from Column B.

    For example:

    B2=01/05/2017

    A5=01/01/2017
    A6=01/01/2017
    A7=01/03/2017
    A8=01/15/2017

    B5=$3.00
    B6=$3.00
    B7=$3.50
    B8=$3.70

    I need the formula to give me the $3.50 price.

    Can you please help me? I know it seems simple, but have been having a hard time with it.

    Sincere regards

  32. I'm ddoing a spreadsheet for my budget of the different bills I owe. My issue is that when I enter new dates for next year it adds 2016 instead of 2017. Can someone tell me how to fix this? I should be able to tpe in the month/date hit enter and the year auto populate but it's doing it for 2016.

    TIA

  33. Thank you for all your wonderful knowledge. Would you be able to help me do the following?

    I need to have a specific starting date such as 1/1/17. I need the formula to calculate 91 days into the future that is a Wednesday closest to the 91st day but not less than the 91 days. The starting date will change every time I need to make an entry, so the formula will calculate the best Wednesday.
    thank you,
    Brian

  34. I am trying to calculate the difference between starting and ending time on a work shift. The calculation is okay if the hours are all in the same day. i.e. start 8:00 am and quit at 5:00 pm.

    The problem I am having is if the starting time is 6:00 pm and the ending time is 3:00 am. I was hoping the answer would be 9 hours. Instead, the answer is 15 hours.

    The cells are formatted in hh:mm. The cells are not formatted like dd-mm-yyyy hh:mm because of the number of employees that need to be entered.

  35. Hello, I am a newbie at this and trying to wing it the best i can to get a jump start. I am trying essentially to calculate 30 day, 60 day, and 90 day increments based on a random start day to arrive at an end day for late fees. I have to of course consider 30 and 31 day months and february of course being a 28 day month this year. Holidays are not an issue. Can you help me figure out how to enter this formula in excell? Thank you!

  36. Sorry I can't find a solution to what I'm looking for in the large amounts of questions. Any help greatly appreciated:

    I'd like a formula that adds a certain value, or indeed a multiplier, based on a date of the month. i.e a cell that adds another €100 automatically every 25th of the month, even better if it can stop after a year (Jan-Dec)

    I'm aware there would be the very long IF function variant using TODAY and a bit of juggling, but there must be a neater formula?

    Would love to hear some ideas. Thanks!

    1. A botched workaround to my above question:

      =IF(TODAY()<DATE(2016,12,31),(ROUND(((TODAY()-DATE(2016,1,1))/30),0)*[desired value]),(12*[desired value]))

      Does the trick, just only comes into effect half way through the month and assumes 30 days in each month.

      Would love to hear how to nail it down to a specific date.

  37. What formula to use if you wants to know age calculation.
    E.g. born on 15th May 2010 and I want to auto calculate age to the date of 31st Dec 2020 ?
    Please help. Thank you

  38. I have a spreadsheet broken down by pay periods and want to apply a formula to change the range of week dates in each subsequent cell. Example: Pay period 1 in January 2017 is 8-14 for the first week and 15-21 for the second. I want a formula to deliver 22-28 in the next cell so I don't have to look at a calendar and manually enter the dates.

  39. Hi,

    What about payroll cut-off date to be published in a cell, say for example cut-off date is from January 1 to 15, 2016. Is there a formula to show this in a single excel cell? Thanks in advance.

  40. Hi,

    The answer to the above question (# 141) is
    =IF((AND(E2"",F2"")),(MIN(TODAY(),F2)-E2+1)/(F2-E2+1),"")

  41. Hi,

    I have to calculate percentage between two dates and the formula I'm using is =(MIN(TODAY(),F2)-E2+1)/(F2-E2+1) which is giving me the desired result. However, when I do not enter any dates in E2 and F2, I get a default result of 4272000% in cell G2.

    I want G2 to be blank when no information is available in E2 and F2, Also it should be blank if I enter dates in one of the cells E2 or F2 or even postdates.

    I would really appreciate if a formula could be devised in coordination with the above percentage formula to achieve the desired results.

    Thanks in advance

  42. I have a start date in A1 and End date in B1 of Construction project, I want first the formula calculate the number of days overdue with today date, and then check if the end date is greater than today then return "Not Due" otherwise calculate number of days overdue.

  43. I wondered if anyone could advise how to add fifteen days to a date and if more than return 'no' in another column with a count of how many days? Similarly with anything less than so for example:

    Greater than:
    Col A
    01/01/16

    Column b
    17/01/16

    Column C
    'No - 1 day'

    Less than:
    Col A
    01/01/16

    Column b
    15/01/16

    Column C
    'Yes'

  44. Great site! I am need of help with a specific date formula. It is as follows:

    I have a specific date that is calculated based on lead times in manufacturing. From that, I have a plant actual ship day of the week. Starting with the specific date, I need to calculate the next plant shipdate as an actual date, not day of week. Is there a formula for doing this?

    Example:
    Specific Date: 12/21/2016
    Plant Ship Day: Tuesday (depending on plant, this can be any day of the week so I have created a drop with all 7 weekdays to chose from)
    Needed: Next available ship date. In this example, 12/21/2016 is a Wednesday so the formula to determine the next Tuesday should equal 12/27/2016.

  45. Hi All,

    I need a formula for create a list of sequential dates.

    I have 2 slicer"Month" and "year" and holiday list also. if i select particular month and year from slicer, i need the end to end dates without holiday list

    For example: If select 2016 and Feb, I need dates without holiday list in column a1 2/1/2016 a2- 2/2/2016 a3- 2/3/2016------ last column 2/29/2016

  46. Im need a formula thta will add 6 month to a date in in cell A2 but if there is no date to return a zero

  47. Hi,

    I'm trying to get number of days between two dates. There are a number of ways to do it, but I'm not able to find one to suit my purpose to calculate vacation days!

    Eg: Cell A2 dates 01/12/2016 and cell B2 10/12/2016. So the person is on leave from Dec 1 to Dec. 10 which is 10 calendar days, but all the formulas I know show it as 9 days.

    Please help.

    Thank you

  48. Hi,

    How to calculate specific date in the next 5th years from a specific date or today's date?

    Regards,
    Santosh

  49. Hello,
    I want to audit if a form was present by the 30th day and by the 90th day. I want a clean spreadsheet to use as a template every month. When I use =B3+30 in one cell and =B3+90 in another cell it returns 1/30/00 since B3 is blank. I want this as my template every month and I would like the formula cell empty until B3 has a value.

    Thank you!

  50. 21/nov/2016 how it will be done with date function or any other function.

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