Excel time

tonelm54
tonelm54 used Ask the Experts™
on
Im trying to convert a set of values from an integer number to Excel time, but cant figure out the conversion.

In Excel I have a value which is stored as '42403.4287037037', which if I use Excel to convert it then it outputs '03/02/2016  10:17:20' which is right, however I need to keep in in the '42403.4287037037' and also convert a date into 'whatever' to get times between times, so I cannot convert the number to date/time in a whole.

I thought it was an EPOCH time, however it has a decimal place in, and if use a convertor such as http://www.epochconverter.com/ the value 42403.4287037037 returns "Thu, 09 Jun 1983 19:18:07" which is completely different.

Anyone have any idea what this value is and how to convert to/from it?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Brian BEE Topic Advisor, Independant Technology Professional

Commented:
The integer of the date value is the number of days since 1900. So the value 1 represents 1/1/1900. The decimal portion is the time in a fraction of a day. For example, if the decimal portion was .5, the time would be 12:00pm (noon)

So to get the amount of time in between two dates, just subtract one from the other. If you are trying to convert the decimal into fractions of an hour, multiply it by 24. There are also Excel functions like hours() and minutes() that might help you.

Author

Commented:
So if I understand right, I need to do 4287037037-42403 which equals 4286994634, which excel equates to #####, and if I convert on Epoch Converter I get Fri, 06 Nov 2105 23:50:34 GMT which isnt correct :-(
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
just put your datetime values inside the text function like this.


=TEXT(42403.4287037037,"ddd dd/mmm/yyyy h:mm AM/PM;@")

text function has two arguments the value itself. either you can hard code it or it can be a cell reference, then second argument of its syntax is the format and you put that inside double quotes.

see the example, i have used.
Book2.xlsb
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Rob HensonFinance Analyst

Commented:
If you need to keep the values in such a format that they can be used for calculations, assuming your '42403.4287037037' is in cell A1:

Formulas in separate cells:
=INT(A1) formatted with suitable date format (mm/dd/yyy or dd/mm/yy depending on location) will show the date.

=MOD(A1,1) formatted as hh:mm:ss will give the time portion.

Thanks
Rob H

Author

Commented:
Sorry, I need to convert back into this number as well, so need to understand how it is constructed.

I know the integer is the mm/dd/yyyy and after the decimal place is hh:mm:ss, but when I convert it using epcoh converter it doesnt match
Top Expert 2014

Commented:
You can format the cell as datetime
Top Expert 2014
Commented:
Do you need to do this in a formula or in VBA?

In VBA, you would use some combination of
CDate()
CDbl()
Rob HensonFinance Analyst

Commented:
If epich is giving incorrect results then don't use it.

Don't understand what you mean by "convert back to this number".
Danny ChildIT Manager

Commented:
Excel actually uses the number behind the date at all times.  It only changes the **display format** of this to show you what you want to see, be it dd/mm/yy or hh:mm:ss ddd mmm yyyy.

Epoch time is Unix-based, and starts from 1/1/1970, so is wildly different.  I don't see how it will help you here.  

if you only want the DATE, not time, just apply a date-only format to the cells.
Or use the INT(cell) function to strip out the decimal part (thus losing the time).
Danny ChildIT Manager

Commented:
note, the whole numbers represent DAYS, and the decimal represents HOURS.  Your 2nd comment above has this round the wrong way.
Danny ChildIT Manager

Commented:
As with most stuff on EE, it's easier if you post a sample sheet.  This should have a moderate range of your typical data, but of course not contain anything that your employer would not want shared.  

Even if you just mock-up your desired output, we can go from there.  

for more info on Excel and time, see this:
http://www.cpearson.com/excel/datetime.htm
Brian BEE Topic Advisor, Independant Technology Professional

Commented:
To refer back to your earlier column, Excel is display the result as #### because either the column isn't wide enough or the resulting number isn't translatable to a date. In this case it's the latter. I tried it myself and got the same result. 4286994634 days since 1/1/1900 is not a date Excel understands.

Your second number, 42403 makes more sense. That is 2/3/2016 as you said.

Don't forget, the date format is just a way of displaying the underlying date serial number.
Brian BEE Topic Advisor, Independant Technology Professional

Commented:
Tonelm54, is there any chance you could provide some insight as to why the answer you accepted solved your problem. You didn't mention that you needed a VBA solution at the beginning.

Thanks,
Brian B (TBone2K)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial