Arithmatic functions with date format...angry results
 

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200901/1

Arithmatic functions with date format...angry results
 

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

"chris23892 via OfficeKB.com" <u39649@uwe wrote in message
news:8fe388a225d78@uwe...
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!!

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200901/1

Arithmatic functions with date format...angry results
 

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"chris23892 via OfficeKB.com" wrote:

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200901/1

Arithmatic functions with date format...angry results
 

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

Pete_UK wrote:
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!!

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200901/1

Arithmatic functions with date format...angry results
 

=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).
--
David Biddulph

"chris23892 via OfficeKB.com" <u39649@uwe wrote in message
news:8fe388a225d78@uwe...
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!!

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200901/1

Arithmatic functions with date format...angry results
 

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


"chris23892 via OfficeKB.com" <u39649@uwe wrote in message
news:8fe3d61e8d31d@uwe...
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 :)

Pete_UK wrote:
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!!

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200901/1

Arithmatic functions with date format...angry results
 

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


"Pete_UK" wrote in message
...
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

"chris23892 via OfficeKB.com" <u39649@uwe wrote in message
news:8fe388a225d78@uwe...
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!!

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200901/1

Arithmatic functions with date format...angry results
 

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

Pete

"T. Valko" wrote in message
...
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


"Pete_UK" wrote in message
...
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

"chris23892 via OfficeKB.com" <u39649@uwe wrote in message
news:8fe388a225d78@uwe...
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!!

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200901/1