Cell formating

SeeCwriter

New Member
Joined
Jul 21, 2010
Messages
2
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?
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Hi

What does that value represent?
It just represents the concatenation of 36256 and 0,3040548611.
 
Last edited:
Upvote 0
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).
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,217,347
Messages
6,136,046
Members
449,983
Latest member
mschaef6

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top