tonelm54
asked on
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.
Anyone have any idea what this value is and how to convert to/from it?
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.
Anyone have any idea what this value is and how to convert to/from it?
ASKER
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 :-(
just put your datetime values inside the text function like this.
=TEXT(42403.4287037037,"dd
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
=TEXT(42403.4287037037,"dd
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
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
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
ASKER
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
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
You can format the cell as datetime
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
If epich is giving incorrect results then don't use it.
Don't understand what you mean by "convert back to this number".
Don't understand what you mean by "convert back to this number".
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).
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).
note, the whole numbers represent DAYS, and the decimal represents HOURS. Your 2nd comment above has this round the wrong way.
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
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
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.
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.
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)
Thanks,
Brian B (TBone2K)
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.