Convert time into hours formula
Hi All,
I've been stuck on an excel extraction I ran through a rostering software.
Basically it pulled
1.00:00:00
1.12:00:00
Day.HH:MM:SS
However when trying to convert it to just hours using numerous different formulas I'm getting name or value errors.
Do I need format the column first before trying to convert it to hours in the blank cell?
I'm stuck on both the formatting of the cell and the formula to convert to hours.
Very frustrating
You get better help on your question if you add a small excel file, without confidential information.
Please also add manualy the expected result in your file.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
I have attached it for you...
Does that help?
c2 =Left(B2,1)*24+Right(Left(B2,4),2) and drag down.
See the attached file.
In c2 ...
=(LEFT(B2,1)+0)+TEXT(RIGHT(B2,8),"HH:MM")
Format C2 as [h] if hours only or [h]:mm if hours/minutes
Or just this...
=--MID(A3,3,99)
format as time
What you need to understand about dates and times in excel is...
a date is just a number representing the number of days passed since 1/1/900...and then formated in a way that we recognise as a date. So, for instance, today (Sun Aug 2015) is actually 42232
Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Please read the forumrules about crossposting.
http://www.mrexcel.com/forum/excel-q...s-formula.html
Please reply.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks