up vote 0 down vote favorite
share [g+] share [fb]

I guess this is two related questions in one question. I'm creating an Excel spreadsheet and I need to enter Qty of Time Spent in the format hh:mm in one cell and in another I'll be calculating the labor cost based on the hh:mm entered in the TimeQty cell. It's possible that TimeQty will exceed 24 hours on some projects so my solution needs to work for this.

It appears that all of Excel's time formats assume that you are referencing a specific date and time on the calendar, not an accumulation of Hours and Minutes.

Anyone have a suggested solution? I also need to be able to calculate hourly rates (or in this case, by the minute) using your suggested solution.

link|improve this question
56% accept rate
If it is over 24 hours, what format are hoping to see? 28:30 for a 28 and half hour job? – lionz Jun 20 '11 at 17:29
Yes, lionz, I would expect to see 28:30 for 28 and 1/2 hours. – HK1 Jun 20 '11 at 18:53
please don't forget to accept the answer if that fits what you asked. Or else, please tell us what's missing. – JMax Jun 23 '11 at 16:15
feedback

migrated from stackoverflow.com Jun 21 '11 at 6:14

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 4 down vote accepted

Use the format [h]:mm:ss in cell TimeQty, and compute cost by =TimeQty*24. The cost cell must then be of numeric format.

link|improve this answer
That works for the display part. Just remember to multiply by 60 (or 1440) to get hour (or minutes) – chris neilsen Jun 20 '11 at 19:27
1  
@chris, Excel stores date/time values such that one day has the value of 1, so 24hours (24:00) = 1.0, so to get the the number of hours from a time value as a raw number the *24 suggested here is sufficient. For example 30:00 converted to number format is 1.25, *24 then makes it 30, as expected. – DMA57361♦ Jun 21 '11 at 9:16
feedback
up vote 1 down vote

Just enter the time as a string in the form "xx:yy" and use this formula to compute the cost:

=(LEFT(A1,2)+RIGHT(A1,2)/60)*hourlyrate

Make sure the hours column is in the Text format.

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.