I got around to have some free time and after carefully reading what you wrote I think I've managed to get a solution for your problem. I've made the following table:
This is somewhat what your tables seems to have - a
Start Time,
End Time,
Total Hours,
Task 001, Task 002, Task 003,
ST and
OT - nothing new here.
What you see on the first 4 columns (from range C->F) is basically how I thought regarding getting the difference of hours for the field
Total Hours. On column
C and
D you have the usual military time (0800, 1600, 0800, 1900). To have theses values show up as correct military time (and to prevent errors in the future) you have to select the cells (in this case
C3:D4 and then go to
Home->Number->Format as Text). The problem here (or at least I saw it as one) is that Excel doesn't automatically detect that those numbers represent the
military time so you have to somehow tell to the program that those numbers represent hours. That is what columns
E and
F are doing , they are taking in the values from the previous cells and "transforming" then in actual time. Here is the formula* that I've used for
E3,E4,F3,F4:
=TIMEVALUE(LEFT(C3;2)&":"&RIGHT(C3;2))
Each cell field in the formulas should be adjusted to the line that we're dealing with. In the following example since E4 is on line 4 you should change the field
C3 to
C4. Please apply these rationale for the remaining code
Applying that formula to each field will net you the values that you see in columns
E and
F. This is good, we now have the correct time! So the column
H is basically the difference between the
Start Time and
End Time. In cells
H3 and H4 you'll find the following (simple) formula**:
Regarding columns
I, J and K I honestly didn't understood - do you fill these manually or do you want them to calculate (in this case equally) for all fields?
If we move to column
M, this is the formula I've used in cell
M3, M4:
=IF(H3>8;8;IF(AND(H3>0;H3<=8);H3;"-"))
What I'm saying here is that if the value of
Total Hours is bigger than 8 then the cell will assume the value of
8. If the value in
Total Hours is bigger than 0 and smaller or equal to 8 the value will also be showed (for example any number between 0-8).
Warning: This will show up as an error if the difference is negative.
In cells
N3,N4 we have the following code:
=IF(AND(M3=8;H3>8);H3-M3;"-")
What I say here is that if
M3 is equal to 8 and
H3 is bigger than 8, then we have here a difference to make! In this case I just make
H3-M3 and the difference will show up in the
OT field. If one of these conditions is not met (for example
M3 is equal to 5) then no "extra hours" were made and the field of
OT will show up as "-".
Let me know if this helped you in any way. Like I said I love Excel and see it as a really powerful tool but sometimes people think that it's too complicated to make certain things - believe me, it is not. I took more time writing you this reply than making the example in Excel, just to give you an idea that for a average user this is a simple thing.
Let me know if I can help you any further!
* If you want the process that I've used to build these formulas, but for now I'll just post what I built
** I've multiplied by 24 to have a result in hours and minutes ( say 2.5 represent 2 hours and 30 minutes)
