Subtracting a date and time from 2 columns

[Sahubba]

Hello,

I was wondering is there a way to subtract a date+time from to columns and have the results in seconds?

Example;
F2 I2 J2
Oct 8, 2012 4:17 PM Oct 15, 2012 1:04 PM Results in seconds

I am using excel 2007
above are how the dates and time are formatted


Thank You for your help.

 

[SkipVought]

hi,

Simply take the difference between the two date/time values. The result is in DAYS.

Then simply convert DAYS to SECONDS: (DAYS*24*60*60)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Sahubba]

Hello,
Just a simple subtraction like F2-I2 does not work for me.
Is there another way I can do the subtraction?

 

[SkipVought]

Then you don't have REAL DATE TIME VALUES! You merely have TEXT!

Convert your TEXT to REAL DATE TIME VALUES using Data > Text to columns choosing the MDY date conversion conversion for each column

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Sahubba]

Hello,
I did as you explain above and still there is no difference.

 

[SkipVought]

I get 6.865972222 days

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Sahubba]

For some reason the cell format wont change.
I even tried format cell and choose a custom formating and nothing happens unless I delete the cell contents and enter the date again.

I paste the dates into excel so I am not too sure if that has anything to do with it.

 

[SkipVought]

Please post the data that is in

F2
I2
J2

on separate lines here so we can be sure of what we are referring to.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Sahubba]

Sure

F2 I2 J2
Oct 8, 2012 4:17 PM Oct 15, 2012 1:04 PM Would be the results

 

[SkipVought]

So you are saying that ...

F2 contains F2 I2 J2

and I2 contains Oct 8, 2012 4:17 PM Oct 15, 2012 1:04 PM Would be the results

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

and nothing is is J2


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Sahubba]

No Sorry
F2 Oct 8, 2012 4:17 PM
I2 Oct 15, 2012 1:04 PM
J2 Would be the results


But I got it to work.
I ended up exporting this excel file to access and converting the data types to a Date/Time and then exported it back to excel.


Dont know what went wrong with the paste.
But it works now.

Thanks for you time and help!

 

[yooneek]

You could also do a format change on the text, converting it into the Date standard.

J2 = (TEXT(I2,"mmm d, yyyy h:mm am/pm")-TEXT(F2,"mmm d, yyyy h:mm am/pm"))*24*60*60



OCD, it’s not obsessive if you can control it…

 

[SkipVought]

@yoo,

Why change a DATE/TIME value to TEXT, that then must be converted back to DATE/TIME to do the arithmetic???



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]