Transform date to seconds

Originally Posted by sflany

I would like to transform date and time-data - presented in one column - (e.g. 12.04.2013 12:00) into seconds. Is there a way how I can manage this?

Number of seconds since when: 1 Jan 1970 (Unix Time, aka Universal Time); or 1 Jan 1900 (Excel time); or (gulp!) since the "beginning of time", that is: 2013 years 12 months 4 days (or do you mean 4 months 12 days?) and 12 hours?

In any case, if A1 displays 12.04.2013 12:00, does =ISNUMBER(A1) return TRUE?

If so, Excel recognizes that as date and time. Write one of the following:

=ROUND((A1-1)*86400,0) for Excel time, because Excel stores date/time as days plus 1 since 1 Jan 1900

=ROUND((DATE(1970,1,1)-A1)*86400,0) for Unix time

The third possibility is not worth demonstrating unless you are truly interested in it. It is difficult to calculate because of the definition of leap year. And it raises some other questions.

If =ISNUMBER(A1) is FALSE, you might try replacing A1 with SUBSTITUTE(A1,".","/") in the formulas above. But that presumes that 12/04/2013 conforms with the date convention on your computer.

Thank you for your answer.
I tried just to define the the column as [ss] for seconds and it seemed to work.

Originally Posted by sflany

I tried just to define the the column as [ss] for seconds and it seemed to work.

Okay. That presents the date in seconds (since 31 Dec 1899). It does not "transform" the date into integer seconds, which is what I thought you asked for.

But the fact that the format worked does tell us that 12.04.2013 12:00 is recognized as (numeric) Excel date/time.

I am a little bit confused - what are integer seconds. Sorry for my ignorance!

maybe in that way ?

Thanks for your help. I will see which of your ideas will guide me to the goal

Originally Posted by sflany

I am a little bit confused - what are integer seconds.

Apparently, porucha's response was useful to you. I could not make sense of it. Perhaps the following will be clearer to someone.

	A	B	C	D	E
1		dd.mm.yyyy hh:mm:ss	formatted as Number
2		12.04.2013 12:34:56	41376.5242592593		C2: =B2
3	formatted as [ss]	3574931696	41376.5242592593	B3: =B2	C3: =B3
4	integer seconds	3574931696	3574931696.0000000000	B4: =ROUND(B2*86400,0)	C4: =B4

actually it was just a nice thank you. I will have a look at the ideas tomorrow