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?
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

shift-del

Well-known Member
Joined
Aug 28, 2009
Messages
516
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,793
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,793
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,694
Messages
5,597,571
Members
414,156
Latest member
WDMix

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