When it comes to calculating dates in Excel, DATE is the most essential function to understand. As you probably know, Excel does not keep the year, month and day for a date, nor does it explicitly store weekday information in a cell. Instead, Microsoft Excel stores dates as serial numbers and this is the main source of confusion.

Not all Excel date functions can recognize dates entered as text values, therefore it's not recommended to supply dates directly in calculations. Instead, you should use the DATE function to get a serial number representing the date, the number that Excel understands and can operate on.

What the Excel DATE function does is return the serial number of a specified date. It has the following arguments:

DATE(year, month, day)

**Year** - represents the year of the date. Excel interprets the year argument according to the date system set up on your computer. By default, Microsoft Excel for Windows uses the 1900 system. In this date system:

- If year is between 1900 and 9999 inclusive, exactly that value is used for the year. For example,
`=DATE(2015, 12, 31)`

returns December 31, 2015. - If the year argument is between 0 and 1899 inclusive, Excel calculates the year by adding the specified number to 1900. For example,
`=DATE(100, 12, 31)`

returns December 31, 2000 (1900 + 100). - If year is less than 0 or greater than 9999, a DATE formula will return the #NUM! error.

**Month** - an integer representing the month of the year, from 1 (January) to 12 (December).

- If month is greater than 12, Excel adds that number to the first month in the specified year. For example,
`=DATE(2015, 15, 5)`

returns the serial number representing March 1, 2016 (January 5, 2015 plus 15 months). - If month is less than 1 (zero or negative value), Excel subtracts the magnitude of that number of months, plus 1, from the first month in the specified year. For example,
`=DATE(2015, -5, 1)`

returns the serial number representing July 1, 2014 (January 1, 2015 minus 6 months).

**Day** - an integer corresponding to the day of the month, from 1 to 31.

As well as month, the day argument can be supplied as a positive and negative number, and Excel calculates its value based on the same principles as described above.

The DATE function is available in all versions of Excel 2019, Excel 2016, Excel 2013, Excel 2010, and lower.

Below you will find a few examples of using DATE formulas in Excel beginning with the simplest ones.

This is the most obvious use of the DATE function in Excel.

For example, to return a serial number corresponding to 20-May-2015, use this formula:

`=DATE(2015, 5, 20)`

Instead of specifying the values representing the year, month and day directly in a formula, you can have some or all arguments driven by of other Excel date functions. For instance, combine the YEAR and TODAY to get a serial number for the first day of the current year.

`=DATE(YEAR(TODAY()), 1, 1)`

And this formula outputs a serial number for the first day of the current month in the current year:

`=DATE(YEAR(TODAY()), MONTH(TODAY(), 1)`

The DATE function is very helpful for calculating dates where the year, month, and day values are stored in other cells.

For example, to find the serial number for the date, taking the values in cells A2, A3 and A4 as the year, month and day arguments, respectively, the formula is:

`=DATE(A2, A3, A4)`

Another scenario when the Excel DATE function proves useful is when the dates are stored in the format that Microsoft Excel does not recognize, for instance DDMMYYYY. In this case, you can use DATE in liaison with other functions to convert a date stored as a numeric string or number into a date:

`=DATE(RIGHT(A2,4), MID(A2,3,2), LEFT(A2,2))`

As already mentioned, Microsoft Excel stores dates as serial numbers and operates on those numbers in formulas and calculations. That is why when you want to add or subtract some days to/from a given date, you need to convert that date to a serial number first by using the Excel DATE function. For example:

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

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

**Subtracting days**from a date:`=DATE(2015, 5, 20) - 15`

The result of the above formula is May 5, 2015, which is May 20, 2015 minus 15 days.

- To subtract a date from today's date:
`=TODAY()-DATE(2015,5,20)`

The formula calculates how many days are between the current date and some other date that you specify.

If you are adding or subtracting two dates that are stored in some cells, then the formula is as simple as =A1+B1 or A1-B1, respectively.

For more information, please see:

And here are a few more examples where Excel DATE is used in combination with other functions in more complex formulas:

In case you want not only to calculate but also highlight dates in your Excel worksheets, then create conditional formatting rules based on DATE formulas.

Supposing you have a list of dates in column A and you want to shade dates that occurred earlier than 1-May-2015 in orange and those that occur after 31-May-2015 in green.

The DATE formulas you want are as follows:

Orange: `=$A2<DATE(2015, 5, 1)`

- highlights dates less than 1-May-2015

Green: `=$A2>DATE(2015, 5, 31)`

- highlights dates greater than 31-May-2015

For the detailed steps and more formula examples, please see How to conditionally format dates in Excel.

Though DATE is the main function to work with dates in Excel, a handful of other functions are available to tackle more specific tasks. You can find the links to in-depth tutorials at the end of this article.

Meanwhile, I'd like to present you our Date & Time Wizard - a quick and easy way to calculate dates in Excel. The beauty of this tool is that outputs the results as **formulas**, not values. Thus you have a kind of 'two birds, one stone' opportunity - get the result faster and learn Excel date functions along the way :)

The wizard can perform the following calculations:

**Add**years, months, weeks, days, hours, minutes and seconds to the specified date.**Subtract**years, months, weeks, days, hours, minutes and seconds from the specified date.- Calculate the
**difference**between two dates. - Get
**age**from the birthdate.

For example, here's how you can add 4 different units in cells B3:E3 to the date in A4. The formula in B4 is built in real-time as you change the conditions:

If you are curious to explore other capabilities of the wizard, feel free to download a 14-day trial version of the Ultimate Suite which includes this as well as 60 more time saving add-ins for Excel.

I thank you for reading and hope to see you on our blog next week!

Excel formulas
CSV
Excel functions
Print
Financial functions
Vlookup
Merge data in Excel
SumIf
CountIf
Excel Compare
If statement
Excel Charts
Pivot Table
Conditional formatting
Excel formatting
Excel duplicates
Excel date
Excel tips
Excel macro
Outlook
Google Sheets
Outlook duplicates
Outlook templates

## 461 responses to "Using DATE function in Excel - formula examples to calculate dates"

how to write date formula

date formula ctrl+:

time gormula ctrl"

For the past year I have been using a formula to keep track of when my patients are due for their next visit. For example, if I visited today and they are due again in 4 weeks, I would plug in today's date and the formula gave me a date 28 days from today. The formula I was using was =SUM(column and row of today's date,number of days to next visit). So it might look something like this: =SUM(C3,28). That was working well up until a few weeks ago when all of a sudden, the projected date was coming up as a series of pound signs (#########). Has something changed with Excel?

Never mind. My future date column was too narrow for double digit months. When I widened it, the pound signs turned into the date. :-S

Yes, the column is to narrow

I have cells formatted as text with a month & day present. I am trying to use the current date to add the current or next year depending on month & day. So for example today's date is 10/10/2016, and I have 2 inputs, 7/1 & 1/1. I'd like to format as 7/1/2016 & 1/1/2017. Any suggestions?

hi,

I want to exclude 6 months from total amount of a special account for making an aging report so I want the result over 180 days of base amount but without 6 months

to get the result but without the last 6 months of this account code (supplier)

I have two dates in two separate column and I want to have which ever date comes due first to populate into a new column. How do i formulate that?

Hi Ryan,

Assuming you are comparing dates in columns A and B, the following formula should work a treat:

=IF(A2

Sum of Column values based on a particular row(person) like On the basis of specfic value like date Oct-2016 it gives us the Sum of other column which we select.

Hi Mam please let me know if 1 serial nos receive in two different date then whose formula use in excel

if i entered date by using formul =Today() the output i want same date -1 but if this date occurs on sunday it should be -2

i have a leads excel (clients) and i plan to calculate how many leads we got:

-yesterday

-today

-last7days

-this month

I have P column that have "7" standard possible answers; like the state of the lead. I need a formula for each state of the lead to count the nr of leads for the above time periods.

Thanks,

I really appreciate this answer i tried for one week to do it.

I tried like this:

=COUNTIF(I2:I11,"TODAY()") + COUNTIF(P2:P11,"=Waiting List")

but it's not taking in count the Date; just sum all of them matching "Waiting List"

Thanks,

Traian

= COUNTIFS(P2:P8,"Waiting List",I2:I8,TODAY())

tried this one and not working as well is saying 0

i have a leads excel (clients) and i plan to calculate how many leads we got:

-yesterday

-today

-last7days

-this month

I have P column that have "7" standard possible answers; like the state of the lead. I need a formula for each state of the lead to count the nr of leads for the above time periods.

Thanks,

I really appreciate this answer i tried for one week to do it

Hi I am trying to create a spreadsheet for vehicle finance showing vehicle, purchase date, purchase price, total interest, number of monthly payments, number of payments remaining, monthly payment, outstanding finance.

The bit I am struggling with is trying to create a formula for a cell to work out how many months are remaining. I know what I want it to do which is work out how many months are between the purchase date and end date using the current date if that makes sense.

I WANT TO FIND REMANING % FROM 100% WHEN GIVEN % IS 17.09

Hi,

I am looking for a excel formula.

Working on incentive program for employees. I am using GDocs for tracking the employee performance.

I am having start date and end date. With the help of start and end date I want to pay the incentives every month. Once the project reaches end date next month should be "0".

Kindly let me know how to write the formula for this.

Note: All the docs are in GDocs & 2 different files.

Thanks in advance.

Regards,

Sharath Babu S

Hello,

Can you please help me out with the following scenario:

I have an expiry date of 30-11-2016 I want to see on next column the date if I subtract 30 days on it e.g. next column should read like 31-10-2016.

Thanks in advance.

Hello,

Since a week i am struggling with an Increment formula,

Im using formula which shows as

=IF(AA2=0,0,(F2)*VLOOKUP($AE2,ML!$F$26:$G$29,2,0))

Here: AA2 is the End of Contract date

F2 is the Basic Salary

Vlookup is the range of Category(Doctor,Admin,Nurses,Paramedical staff) which define the percentage in Increment.

Right now i have months in different coloumn

i want this formula to show only for those people whose dates will reflect on the End of Contract months.

for me this formula is effecting in all the months.

Thanks in Advance

I need a formula to add a year to date if a specified field has "Y".

so if field A1 equals "y" then add a year to the date on A2.

Hi

I need to set a formula which help me to auto calculate the expiry date

eg : start date is 6 Apr 15 and the expiry date is 2.5 years later

and i will use this formula for the other cells

do i need to put the $ so that i can just drag it down

thanks

If I don't have "B" then I want my result to be a "0".

How can I do it???

Example:

If I want to get the days since something is open and one of the dates is empty.

"A" Date of report (I will always have this information)

"B" Date the action is open (I will not have always this information)

my formula is

="A"-"B" = days since it has been open.

If I don't have "B" then I want my result to be a "0".

How can I do it???

I found the answer :-)

=IF(B="", 0, A-B)

Can you help me out with following problem.

I want to calculate total delay time and the total early time in the attendence sheet.as a example,our office start at 7:00AM. if some one came at after that time or early that time,I want to calculate total delay times and early times for a month.

please help me

Good day,

Can you maybe help. I have a spreadsheet with the age of issues (issue log). I need to split the ageing of the issues into 60 days to count how many of the issues in each of the ageing falls to present it in a pie chart at the end.

How do I calculate the counts for each ageing categories?

Thank you,

Leana

Sorry, I noticed the detail is incorrect - apologies.

It must be split between 60 days.

Sorry, it seems like it converts my calculation....

It reads: It must be split between less than 30 days, 31-60 days and more than 60 days

Hi Leana,

Please provide more information on how your data is organized. In particular, how should the aging of issues be calculated? Do you have a column of issue dates that should be compared with today's date?

How do i get a formula to change a date (12/25/10) to a # (dpd)

Hi Im trying to populate a calendar from one date.

eg if I enter the 20th of november this will create a number of dates and jobs based on formulas from the entered date

eg 20.11 start date will mean on 20.11 + 83 job A needs done and 20.11 + 123 job B will need done

Is this Possible?

Hello,

I am trying to search for any date in three columns and add 2 years to the date it finds. any suggestions?

Hi,

I am trying to use a formula to automatically calculate the length of time until the next meeting based off today's date. The dates of the monthly meetings will be stored in a separate worksheet. So far I believe something like the formula below should work?

=MIN(IF('Schedule V.1.1'!D13:D22>=TODAY(),'Schedule V.1.1'!D13:D22,""))

Any help would be greatly appreciated.

Hi,

I had prepared a Attendance Sheet which is auto populated with P (present) till present date and A (Absent) if entered manually in another sheet named "Absent" with the following formula: =IF(E$2TODAY(),"",IFERROR(VLOOKUP($A4&"-"&E$2,Absent!$A:$E,5,0),"P")))).

My query is What is the formula for making a series of A (Absent) if one of an employee is absent from a given start date to given End date in sheet "Absent". Also what is the integrated formula for an employee who had retired /resigned on a specified date

Thanks for your support

Dear All

I want to maintain a expense sheet which contains cash expense and credit expense but the problem is about the preparing for merge of cash and credit how i can please let me Know

Hello, I would like to have an automatique date put in when i put a X in a colume. But I do not want that date to change once it is put it in. I tried this but once we change date the date in the colume changes to and i don't want that.

=IF(G4="x";NOW()) -- changes date the next day

=IF(G4="x";Today())-- changes date the next day

Hello ,

I am trying to develop a spreadsheet that I can input the date and automatically generate the date of the 6 month review ... is that possible ?

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

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!

Hi,

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

Regards,

Santosh

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

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

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

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.

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'

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.

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

Hi,

The answer to the above question (# 141) is

=IF((AND(E2"",F2"")),(MIN(TODAY(),F2)-E2+1)/(F2-E2+1),"")

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.

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.

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

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!

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.

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!

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.

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

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

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

Hi,

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

Thank you.

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

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.

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.

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

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

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

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

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.

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?

01/01/2017 14:03:12

how to single Cell IN EXCEL pls help me

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.

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!

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.

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.

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?

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

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

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

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

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?

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

I have found one that works!!! Thanks

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

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.

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

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.

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?

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.

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!

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.

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.

HELLO DEAR

I WANT SHOWN DATE IN EXECL IN BELOW PATTERN

12/05/2016

I WRITE 120516 AND ITS SHOW AS 12/05/2016

Hello Everyone

I've been looking everywhere for help to my edate error but cant find anything that works. Hope someone here can help.

I am trying to use edate to subtract 12 months from a given date to use in a look up calculation for Fiscal YTD results. Everything works fine until February 2017 rolled around and the results give me February 28, 2016 not February 29, 2016. Which causes a #N/A error.

=IF(B8=EDATE(Q3,-12),B10,SUM(B10:INDEX(B10:Y10,1,MATCH(EDATE(Q3,-12),B8:Y8,0))))

Is the formula I am using which works fine for every other month.

Q3 refers to the current month end date: in this case: 2/28/2017

Row 8 has dates in subsequent cells as follows:

7/31/15 8/31/15 9/30/15 10/31/15 11/30/15 12/31/15 1/31/16 2/29/16 3/30/16 etc all the way up to my 2/28/17 month.

the formula returns: 2/28/16 not the 2/29/16 I need to get my prior fiscal year to date total.

In other words my formula returns the date as 42428 not 42429. Which the causes the formula to error out since there is no 42428 in the date cells.

Thanks in advance for the help.

Hi

i'm looking for a formula to get service years on a specific date. For example, joining date 01/01/2015 in cell A1, what is the formula to get years on 31/12/2016 ?

I figured out how to calculate dates based off of a start date, however, when that initial cell is blank, it fills in random dates that I don't want to be visible. Is there a formula that I can embed into the cell so that they stay blank till the initial cell is filled?

Hi, Johanna,

if the initial cell is A1, try the following:

=IF(ISBLANK(A1),"",FORMULA)

Note, that instead of the word FORMULA you put that formula of yours to calculate the date.

Hello,

I have a table with formulas containing dates in them. Is there a way that these cell formulas automatically update with new results every time I put in new weekly dates in a separate column?

(Example: Columns S6 till S12 - Sunday, 3/26 till Saturday 4/01)

hi, can you help me with some date formula, i wanted to put formula in cell A1 where when you write anything on cell B1, the cell A1 will automatically generates a date for that day.... and on the next day, when you write on cell B2 the the cell A2 will automatically generate a date on the same day but will not affect the cell A1... meaning if today is January 1, 1900 and write anything on cell B1, cell A1 will be dated January 1, 1900 and when i write tomorrow on cell B2, cell A2 will be dated January 2, 1900 but cell A1 will still be January 1, 1900.... hope you can help me.

Hi,

I got a calculation to do in excel and I am completely stuck. I need to calculate if a staff is eligible for local leave or not. The staff is entitled local leave after 1 year (365days). So, if I have a date of entry 9/04/2017 in the next cell it should say YES if not 'NE' Not Eligible.

Can someone help me on this please?

Br,

John

Hello, John,

assuming that the dates are in A row, you need to calculate the following:

date of hiring (A1) – date of entry (A2) = result (A3).Try the next formula in A3:

=IF((A2-A1)>=365, "YES", "NE")

Hello please help. What is the calculation for this. I want to get the average and/or sum.

ColumnA: 15 days, 1 hours

ColumnB: 15 days, 6 hours

Thank you

Hello,i please need assistance.We award employees at 5 year intervals.Please help with formula to use.

Hello, Spinky,

If the starting date is in A1, then you place the following formula in A2:

=DATE(YEAR(A2) + 5, MONTH(A2), DAY(A2))

Check the link to see DATE function in more details.

Hello,

I am attempting to calculate the inventory value based on the inventory quantity (in column B) and today's unit price (columns C through I represent the days of the week and have unit prices below each day for each item; I have a new sheet for each week). I want the inventory value to display in column K following each type of item in my inventory (column A for each item).

hi, pls i will like you to help me with the steps on how to convert text into numerical values Using Excel 2010.

A former co-worker created a spreadsheet for our reimbursement use but the calculating/formula fields are locked down and I don't have the password. I need to recreate this spreadsheet and one of the fields/formulas has me stumped.

Column A is a date column for manual entry by users. Starting at row 9 users enter a single date (04/25/2017). Single dates can be entered up to row 54.

The formula I need is for a period of time, based on the starting and ending dates found in column A.

This is what the locked field looks like with no data in column A: "From 1/0/00 to 1/0/00"

This is what the locked field looks like with data in column A, row 9 (1st row of data) & row 12 (last row with data): "From 04/24/2017 to 04/25/2017".

My TEXT formula is not correct

(=TEXT(A9,"mm/dd/yy")&" - "&TEXT(A54,"mm/dd/yy")

The result is this:

"04/20/2017 - 01/00/00" (even though I have data/dates in rows 10-17)

I'm not sure how well I have explained myself but any help is greatly appreciated. Thanks!

I want to know how to remove date formula to enter other date beyond a particular date example i want to entre 10-05-17 but formula is set for 07-05-17

Trying to complete task that is completed by completion date with a total of percentage.

10 task starting at B2-B11 and dates are entered to show completed how can I get B12 to show total percentage completed??

A 2-12 has names of task

any help is appreciated

Hi, I have written a macro to process dates as the X axis and the value as the Y axis. My challenges are that the length of column of dates could vary so some cells in the plotting range of the Macro could be empty (this returns default date in that cell of 01/01/1900. So my first question is how to use the VBA code so that the range is only taken with all cells containing a date and blank cells are ignored?

Next the macro will format the graph but i cant find a way to arrive at the X Axis minimum and Maximum Scale limits, if i use the "auto" function then the graph does not display until I reduce the Min and Max date number to either side of the actual date range. I thought i could use the DATEVALUE function to set the date value number as an integer and use that in the MIN and MAX graph formatting Axis code: "ActiveChart.Axes(xlCategory).MinimumScale = 42817" and "ActiveChart.Axes(xlCategory).MaximumScale = 42823". Replacing the number with the Integer for the start date and end date of my range.

Any suggestions on the above, I have searched a lot but not found the answer yet.

Hello :)

Please let me know, How can I segregate the COLORED dates in to different columns.

Recently i did it manually but its very time taking procedure, please guide me to find an easy way.

how to date format change

ex: 01.01.2017

i need 01-Jan-17 format

Hi there :)

What is the best excel formula to use, to calculate the actual date based on the xx number of days + a specific date, where its counted as day 1?

The formula I'm using currently, worked correctly on some dates, but not on others, it's one day less. Could this be due to the Stat holidays within the range?

For example,

a. 2 days starting from Aug 5, 17 = Aug 6, 2017

b. 3 days starting from Aug 5, 17 = should be Aug 7, but the same formula I used for "a" returned Aug 6

c. 7 days starting from Aug 5, 17 = Aug 11, 17, but returned Aug 10.

Please help! What am I missing....

Hi

I want to count a Today date in a particular cell,Suppose my all data in sheet1 and and i prepare a formula sheet on sheet2,in that i want a count from a cell Today date only(as Like so many date are there in that i want to count how many thing dispatch on Today date),so that directly reflect to my sheet2.

Great info that you have here. Please help. I am working on my sheet of manage data,I want excel to series easily for me

kindly help me

how can i put the series in this way

for example

01/2017

02/2017

03/2017

main thing is that 01, 02, and 03 is not date or month

this is my work sheet serial number i have alot of work sheet thats way i m manage one sheet for inform me to whos my work done or not

so kindly help me how can put serial

01/2017

02/2017

03/2017

04/2017

05/2017

continues....