Cell formating

[SeeCwriter]

If cell A1 has date 4/6/1999, cell A2 has time 7:18:33, and cell A3 is set to "=A1&A2" its value will be 362560.304548611111111. What does that value represent? Is it the number of seconds from January 1,1970, perhaps?

 

[shift-del]

Hi

What does that value represent?

It just represents the concatenation of 36256 and 0,3040548611.

 

[AlphaFrog]

Dates And Times In Excel

362560 is the number of days since Jan. 1, 1900

.304548611111111 is the decimal portion of one day that 7:18:33 is.

 

[xenou]

362560 is the number of days since Jan. 1, 1900

.304548611111111 is the decimal portion of one day that 7:18:33 is.

Strictly speaking:
36256 is the number of days since Jan 1, 1900,
and
0.30454861111111 is time value.

You might find these instructive:

=A1&""
=A2&""

Excel is using the "underlying" numeric time value in the concatenation (as in the link). If you did want text, then:
=Text(A1,"m/d/yyyy")&" "&Text(A2,"hh:nn:ss")

Also, Excel uses some funny dates for the first two months of the year 1900 due to an error in the leap year calculations - so Excel actually starts out a "day behind" so to speak, and is back in order on March 1, 1900. This bug was actually originally in Lotus 1,2,3 but was kept in Excel for compatibility (back in the day, Lotus was used a lot in spreadsheet software).

 

[SeeCwriter]

Does Excel have a built-in way of converting a date & time pair to the number of seconds since January 1, 1970? I believe this is called UTC time.

 

[xenou]

This may help (original formula is for OOorg but below is an Excel version in the comments, as well as a formula to do the same in reverse):
http://emspace.com.au/article/how-convert-date-string-excel-unix-timestamp

In theory, it shouldn't be too hard to work it out yourself - just the number of days since Jan 1, 1970 * 86400 I believe (for complete days, anyway) - of course, times and dates are always a tricky thing. You can test your result here:
http://www.epochconverter.com/

Since unix uses this time measurement, you can also search for functions and formulas to convert Excel/Excel VBA time to Unix time.