Time Conversion

[jcfite]

I have an spreadsheet that is monitoring the productivity of agents, as well as the time that it takes to complete each task. My workforce keeps time as .50 = 30 minutes. So 7 and half hours is actually 7.5. Agents are putting the actual time of it took such as 15 minutes however I would like to insert a formula that will convert this for me manually. I know I could give them a conversion sheet, but it took quite a while to get them to actually be able to work in excel. So I would like to add this formula. Any suggestions would be greatly appreciated.

jcfite

 

[c_m_s_jr]

As long as they are putting in just the 15 or 30 or 45 etc....

Couldn't you just do A2/60?

Where A2 houses the time it took?

 

[lenze]

Hi and welcome to the board!!
Perhaps my VBAExpress KnowledgeBase article Converting hr:min to Decimal Hours on the Fly will prove useful?? You can use the Code to automate the proces, or the formula by itself.

lenze

 

[jcfite]

No, I have a main sheet that is computing all of the total. So lets say that it took 7 and half hours. They are putting it in as 7.30 for 7 hours and 30 minutes. However I need for it to read 7.50 for 7 hours and 50 minutes. So lets say they put 7.30 in A1 in A2 I would like for the time to be 7.50.

 

[jcfite]

Thanks, the Query failed! - Lenze

 

[c_m_s_jr]

Well I don't know how to do it in VBA, but in a helper column you could put:

INT(A1)+((MOD(A1,INT(A1))*100)/60) which will convert the time like 7.30 to 7.5 and 7.15 to 7.25, etc.

 

[lenze]

Maybe that board is down?? Try this link
http://www.vbaexpress.com/kb/getarticle.php?kb_id=882

lenze

 

[jcfite]

Thanks c_m_s_jr, that works perfect!!! Lenze I was able to get into that site, will take a look.

 

[jcfite]

c_m_s_jr - lets say they put in .15 for 15 minutes. How would I get that to convert to 25 minutes. with the formula you gave?

 

[barry houdini]

Here's one way...

=SUBSTITUTE(TEXT(A1,"0.00"),".",":")*24

or using DOLLARDE function from Analysis ToolPak

=DOLLARDE(A1,60)

....or another

=INT(A1)+MOD(A1,1)*5/3