Excel formula help please

Gers

I'm trying to calculate the number of days between the two dates and have copied the formula directly from the MS support website but it's not working. Can anyone tell me how I've gone wrong please? Thanks.

Mine:

MS's:

la531983

Try changing the number format of the 3rd cell to just a plain number.

400ixl

That should work and if the cells are formatted as general which is default then that is fine.

Click on the fx to the left and that will bring up the functional arguments box and check it looks right there and if not it may give clues.

tizerbelle

As a tip for future, you didn't need to use the datedif formula for this.  A simple  =B1-A1     would deliver the same answer and generally no number format issues either.   

Note though, these formulas whether simple or the datedif exclude the start date from the calculation so sometimes you need to add that back in - for example if you want to know how many days someone was employed for and they started on 1 Jan and left on 10 Jan.   the formula will return 9 but they were actually employed for 10 days in total.  So the fix for that is simply a +1 at the end of the formula   =B1-A1+1     or    =DATEDIF(A1,B1,"d")+1

tizerbelle

Also - the dates in the MS example are written in the US format mm/dd/yyyy so if you wanted to compare answers you'd need to use 6 May 2016 as the end date! 

Rodders53

C  =B-A works for me as a simple sum...

DATEDIF isn't a recognised function name in my (old) version of Excel...

Gers

la531983 said:

Try changing the number format of the 3rd cell to just a plain number.

Thanks - that made no difference.

400ixl said:

That should work and if the cells are formatted as general which is default then that is fine.

Click on the fx to the left and that will bring up the functional arguments box and check it looks right there and if not it may give clues.

Thanks - that made no difference either.

tizerbelle said:

As a tip for future, you didn't need to use the datedif formula for this.  A simple  =B1-A1     would deliver the same answer and generally no number format issues either.   

Now that worked! Thanks.  No idea why MS didn't offer that one up or why theirs didn't work. Anyway, sorted now. Thanks to all those who responded.

DullGreyGuy

Gers said:

I'm trying to calculate the number of days between the two dates and have copied the formula directly from the MS support website but it's not working. Can anyone tell me how I've gone wrong please? Thanks.

Mine:

If you look at the associated help file you'll learn that DateDif isn't really an Excel formula but something they did to enable users of Lotus 1-2-3 to migrate across to Excel without having to change their spreadsheets and hence it doesn't auto-complete as you're typing the function name and isn't in the list of functions.

To get the date difference in days you can simply subtract one from the other (ie =A1-B1) or the other way round depending on if you want the number to be negative or positive.  

Gers

Rodders53 said:

C  =B-A works for me as a simple sum...

DATEDIF isn't a recognised function name in my (old) version of Excel...

  This is where I got the formula from.

It's headed:

Excel Help & Training

Common uses of functions Calculate the difference between two dates

Weird.

Heedtheadvice

Not needed to sort out your problem now but could be useful info for another rime.....

The datedif formula look fine and should work from earlier than the 2003 version.  Can't remember from when but as noted is an early 'hangover function and not necessary for  simple day count.

It is useful though for other date calculations such as weeks months years etc so very  flexible.

The simple B-A calculation might give unexpected results if time is in  the date cell and hidden in the result cell ( cell formated not to show decimal) and subsequently used in  a calculation.

Having a #Name error, I wonder if you do not have that in your formula list ( click the = sign to find out ) or perhaps, did you use the double quote symbol or two single quotes?

double_dutchy

As others have said, DATEDIF probably isn't the best function to use here in any case.....but if you get #NAME? error as shown then that might mean there's something wrong with the quote marks in the "d" part (they look a little odd on your screenshot).

Did you say you copied the formula? What happens if you just type it in afresh, does it work then?