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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

shift-del

Well-known Member
Joined
Aug 28, 2009
Messages
583
Office Version
  1. 365
Platform
  1. Windows
Hi

What does that value represent?
It just represents the concatenation of 36256 and 0,3040548611.
 
Last edited:

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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:

SeeCwriter

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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,880
Messages
5,834,222
Members
430,265
Latest member
CDane

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
Top