1

I have an employee work schedule all set up in Excel 2016, Windows 10. Cell C3 is “Time In”, C4 is “Time Out”, and D3 is the total number of hours for that day (i.e., I enter In 10:00 AM in C3, and Out 8:00 PM in C4, and D3 automatically gives me 10 hours). Very nice.

However, the D3 cell, total hours for the day, is in Time format, therefore I can't (or can I?) use it as a number for a Sum function at the end of the row – in other words, adding up those cells to get the total hours for the week, Monday hours + Tuesday, ...). Is it possible to have a cell, say D4, which would automatically copy the value in D3, but paste it into D4 as a Number format, so I can take all my row-4 cells, add them up and get total hours for the week. Then, I can very simply plug in the hourly rate, and get my total cost of labor for that week. I'm so impressed that I was able to make this sheet following tutorials, but now I'm stumped.

  • This answer and the comments that follow explain the handling of time cells. – AFH Apr 9 '18 at 23:12
1

In Excel Time format, 1 represents 1 day — so the standard way of converting Time values to hours is to multiply by 24.  So you could set D4 to =24*D3, or simply multiply the SUM by 24.

You’ll want to manually format the affected cells as “General” or “Number” to get them to display correctly.

-1

To accomplish the job, follow these steps:

  1. Put Time value in both C3 & C4.
  2. Format both cells as h:mm:ss AM/PM.
  3. In C4 enter time like 22:00, excel will convert it into 10:00PM.
  4. To count difference between two Times as a Decimal number, use this formula in D4.
  5. =INT((End time - Start time) * 24).
  6. Select D4 & format it as Number without Decimal place.

You find values like shown below.

Start Time:   10:00:00 AM    Time difference in Decimal   

End Time:     10:00:00 PM    12

D4 = INT((C4-C3)*24)

Other possible solution is,

D4=Text(C4-C3,"H") you get 12.
  • This answer is much too complicated. Note that Scott answered this with only two sentences. The OP has already done Steps 1 and 2, as is obvious from reading his question. Step 3 is totally unnecessary to mention. Finally, your first formula assumes that OP wants to discard the fractional part of the hours worked. This is unlikely, since he's calculating labor cost. Your second formula is also unusable since he wants to use the result in a calculation. – Bandersnatch Apr 10 '18 at 13:13
  • 1
    Okay,,, point to be noted,,,nXt time I try to answer better than above one :-) – Rajesh S Apr 11 '18 at 8:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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