Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

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

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

    
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

2 Answers 2

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.

share|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

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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