Very new to Excel, Trying to Calculate Hours from a specific time

[Sledge007]

Hello All,

I'm pretty well versed with computers, however my spreadsheet experience is basically nil. I have figured out some basics on my own. Here is my problem:
I need to record the hours as they are on the sign in sheet, but employees get paid from 7:00AM.

I used the following formula to calculate my hours:
=IF(B3<>"", sum(F3-E3,H3-G3,J3-I3,L3-K3,N3-M3,P3-O3)*24,"")

There is probably a shorter formula I could use, but this is what I figured out in the 3 days I've been working with spreadsheets.

As you can see there are two entries at the lower left corner that are signed in at 6:50 and 6:30 AM however they don't actually get paid until 7:00AM.

Thanks!

[dflak]

Where you have the start time (for example E3) use the MAX formula
=SUM(F3 - MAX(E3,TIMEVALUE("7:00 AM"))+ ...)
This will give whatever time is logged in but not earlier than 7 AM.

[Sledge007]

Where you have the start time (for example E3) use the MAX formula
=SUM(F3 - MAX(E3,TIMEVALUE("7:00 AM"))+ ...)
This will give whatever time is logged in but not earlier than 7 AM.

Thanks dflak, that helped me, but it's still off.
This is what I have for a formula now:

=IF (B3<>"", sum((F3 - MAX(E3,TIMEVALUE("7:00 AM"))+H3 - MAX(G3,TIMEVALUE("7:00 AM"))+J3 - MAX(I3,TIMEVALUE("7:00 AM"))+L3 -
MAX(K3,TIMEVALUE("7:00 AM"))+N3 - MAX(M3,TIMEVALUE("7:00 AM"))+P3 - MAX(O3,TIMEVALUE("7:00 AM")))*24), "")

My problem now is that it is calculating -7 hours for days that people are not at work. So for example a person works one out of six days, the 5 days they did not show up for work they have an accumulated 5 days times 7 hours for a total of negative 35 hours. How can I fix that?

Just FYI, I have only put the formula on Row 3, as I am using that Row as a testing ground, until I get it right, before I apply it to the remaining rows.

[dflak]

You will probably have to change this:
(F3 - MAX(E3,TIMEVALUE("7:00 AM"))
to IF(ISBLANK(F3),0,(F3 - MAX(E3,TIMEVALUE("7:00 AM")))
In other words, if you don't have a punch out time, return zero hours - if you do, then do the calculation. These things get complicated fast every time you add another condition.

[Sledge007]

Thanks dflak. I appreciate your effort. I called around to some friends and we came up with this solution:

I got rid of the IF(B3<>"","") part and wiped out the conditional formatting altogether. Then made a conditional format that if a cell ended in 0.00 that the text is white, which eliminates the zeros.

We then used this formula to achieve the desired effect, which I can confirm works exactly as intended:

=sum((IF(E3="",0,if(E3="7:00 AM",F3-E3,F3 - MAX(E3,TIMEVALUE("7:00 AM")))*24)))+((IF(G3="",0,if(G3="7:00 AM",H3-G3,H3 - MAX(G3,TIMEVALUE("7:00 AM")))*24)))+((IF(I3="",0,if(I3="7:00 AM",J3-I3,J3 - MAX(I3,TIMEVALUE("7:00 AM")))*24)))+((IF(K3="",0,if(K3="7:00 AM",L3-K3,L3 - MAX(K3,TIMEVALUE("7:00 AM")))*24)))+((IF(M3="",0,if(M3="7:00 AM",N3-M3,N3 - MAX(M3,TIMEVALUE("7:00 AM")))*24)))+((IF(O3="",0,if(O3="7:00 AM",P3-O3,P3 - MAX(O3,TIMEVALUE("7:00 AM")))*24)))

Works like a charm.

[FDibbins]

You could probably shorten/simplify that formula by using 7/24 instead of TIMEVALUE("7:00 AM"). Or better still, put that in a cell some place and reference it

[dflak]

Or make a name with that value .

I do not know for sure, but I suspect that 7/24 evaluates faster than TIMEVALUE("7:00 AM"). Your suggestion of putting the value in a cell and referencing it may be a good one. I also suspect that looking up a value might take a bit more time than calculating it, but if you use that value more than once, there should be a point where calculate once, look up multiple times is faster especially for very complicated formulas.

The key word for both of these statements is "suspect." I really have no proof or benchmarks.

For this particular application, I doubt that any performance issues will be seen due to the small amount of data.

[daddylonglegs]

You could utilise the "Time in"/"Time out" values in row 2, e.g. with this formula in Q3 copied down

=24*(SUM(SUMIF(E$2:P$2,{"Time Out","Time in"},E3:P3)*{1,-1})-SUMPRODUCT((E$2:P$2="Time in")*(E3:P3<7/24)*(E3:P3<>""),7/24-E3:P3))

The first part gives you the hours as written and the SUMPRODUCT part makes an adjustment for the times before 07:00

or this shorter version should also suffice

=24*(SUMPRODUCT(F3:P3-E3:O3,(F$2:P$2="Time out")+0)-SUMPRODUCT((E3:P3<7/24)*(E3:P3<>""),7/24-E3:P3))