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.
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?
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
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.
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
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
Do you need to do this in a formula or in VBA?
In VBA, you would use some combination of
CDate()
CDbl()
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
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)
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.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
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.