Date Formula

[KN3M3SIS]

Date 1 Date 2

2003-11-13-14.17.16.000000 2003-11-14-16.02.55.000000
2003-11-06-08.14.32.000000 2003-11-06-10.28.03.000000
2003-10-31-13.22.19.000000 2003-11-13-12.14.07.000000

I have 2 fields (Date 1 and Date 2). I need a forumla to calculate the numbers of days between the 2 dates. So Date2-Date1= #.# days.

Please advise.

 

[carrr]

http://support.microsoft.com/?kbid=214134

 

[KN3M3SIS]

I guess an additional question is what is needed to get the date format properly formatted to properly calculate the difference.

Right now they are reading in:
Year-Month-Date-Hour.Min.Sec.000000

Please advise.

 

[SkipVought]

Hi,

It's a simple formula...

Assuming that your data is in columns A & B

=B1-A1

format the value as General or Number with however many places.

Date/Time is just a number. Date in Excel starts with 1/1/1900 and incriments by 1 each day.

For instance 37959.52317 is 12/4/2003 12:33 (formatted m/d/yyyy h:mm).

But I could just as easily change the FORMAT -- NOT the value -- to...

Thrusday (dddd)
Thu Dec 4, 2003 (ddd mmm d, yyyy)

and on and on.








Skip,

http://www.TheOfficeExperts.com

 

[KN3M3SIS]

The cells as they aren't won't reformat to fit what's required. Is there a way to change/fix this without re-entering all of the data manually?

Thanks

 

[SkipVought]

What is your objective?

Skip,

http://www.TheOfficeExperts.com

 

[KN3M3SIS]

My objective is get

(2003-11-14-16.02.55.000000)-(2003-11-13-14.17.16.000000) = Difference in days.

I am unable to change the format to properly convert it so is there a formula or something that will trim and reformat so that the date function will work?

 

[SkipVought]

Where are your 2 date/time values?

Exactly what is the formula that you are attempting to use?

Skip,

http://www.TheOfficeExperts.com

 

[KN3M3SIS]

I havent' attempted to use a formula yet. I need a formula that will find the difference between the two dates and return the number of days.

 

[carrr]

This is working on my machine...

A1=2003-11-13-14.17.16.000000

B1=left(a1,10)

C1=B1*1 (cell formatted to date gets you the appropriate date of 11/13/200#)....but you'd probably want to stop before formatting the cell and settle for the raw number of 37938 and do your subtraction on it....)

 

[SkipVought]

Did you read my post above???

Assuming that your data is in columns A & B

=B1-A1

format the value as General or Number with however many places.



Skip,

http://www.TheOfficeExperts.com

 

[KN3M3SIS]

Yeah, but your forumula didn't account for trimming the cell down. I attempted for format the cells but nothing changed.

The new formula worked GREAT. Thanks to all.

 

[SkipVought]

Gees, I am really sorry! I missed the whole point

What you have are NOT dates -- they are strings that have some date representation to you.

What you ought to do is convert your data to Excel Date/Time form and THEN the manipulation/calculation will be ALOT simpler.

I parsed your data/time strings into excel Date & Time using the Text to Columns Wizard.

It would be a mistake, unless this is a quicky, to leave your data in this format.

;-)


Skip,

http://www.TheOfficeExperts.com