Excel time

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?
tonelm54Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian BEE Topic Advisor, Independant Technology ProfessionalCommented:
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.
tonelm54Author 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 ExpertCommented:
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
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Rob HensonFinance AnalystCommented:
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
tonelm54Author 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
aikimarkCommented:
You can format the cell as datetime
aikimarkCommented:
Do you need to do this in a formula or in VBA?

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
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 ManagerCommented:
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 ManagerCommented:
note, the whole numbers represent DAYS, and the decimal represents HOURS.  Your 2nd comment above has this round the wrong way.
Danny ChildIT ManagerCommented:
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 ProfessionalCommented:
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 ProfessionalCommented:
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)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.