Arithmatic functions with date format...angry results

[chris23892 via OfficeKB.com]

Can anyone tell me how to do the following:

I have a col. A and Col b with dates in the following format:

12/5/2008 12:00:00 AM and 11/19/2008 0:00


What I need to make a formula in the next col that will tell me how many days
apart these two dates are. Basically, just need to subtract the two dates
and display the number of day between the two dates..


Any thoughts on how to do this?
Thanks a bunch for all the help!!

 

[Pete_UK]

As long as those are entered as true date/times in Excel, you could do this:

=INT(B1-A1)

and format the cell as General.

Hope this helps.

Pete

 

[Luke M]

Note that 12:00:00 AM is treated the same as 0:00:00.
A formula of
=A2-B2
will work, just need to format the cell to general or number, as XL will
default to thinking you want a date.

 

[chris23892 via OfficeKB.com]

You rock! Worked like a charm. I was using =ABS(B1-A1) and not formatting it
as genral.....gave me strange date format results.

Your's worked great

As long as those are entered as true date/times in Excel, you could do this:

=INT(B1-A1)

and format the cell as General.

Hope this helps.

Pete

Can anyone tell me how to do the following:

[quoted text clipped - 9 lines]

Any thoughts on how to do this?
Thanks a bunch for all the help!!

 

[David Biddulph]

=B1-A1, and format the result as number or general.
Make sure that your input cells are really Excel date/time, and not text.
If, for example, you have a non-breaking space CHAR(160) instead of a real
space, Excel will treat it as text. If in doubt, check with
=ISNUMBER(cellref) and =ISTEXT(cellref).

 

[Pete_UK]

You're welcome, Chris - thanks for feeding back.

Dates are stored in Excel as integers (number of elapsed days since some
reference date), whereas times are stored as fractions of a 24-hour day.
Hence you don't need the fractional part. You might also consider this
variation:

=INT(B1)-INT(A1)

again, formatted as General.

Pete

You rock! Worked like a charm. I was using =ABS(B1-A1) and not formatting
it
as genral.....gave me strange date format results.

Your's worked great

As long as those are entered as true date/times in Excel, you could do
this:

=INT(B1-A1)

and format the cell as General.

Hope this helps.

Pete

Can anyone tell me how to do the following:

[quoted text clipped - 9 lines]

Any thoughts on how to do this?
Thanks a bunch for all the help!!

 

[T. Valko]

I guess it depends on what result they're looking for:

1/1/2009 11:59 PM
1/5/2009 11:00 AM

=INT(B1-A1) = 3
=INT(B1)-INT(A1) = 4

--
Biff
Microsoft Excel MVP

As long as those are entered as true date/times in Excel, you could do
this:

=INT(B1-A1)

and format the cell as General.

Hope this helps.

Pete

 

[Pete_UK]

I pointed this out in my follow-up reply, but thanks for giving the example.

Pete

I guess it depends on what result they're looking for:

1/1/2009 11:59 PM
1/5/2009 11:00 AM

=INT(B1-A1) = 3
=INT(B1)-INT(A1) = 4