T
tjtjjtjt
I have a spreadsheet to track time worked per day for every day of the year.
There are 3 possible time slots defined by six Columns:
[Day In(Column D)--Lunch Out(Column E)]
[Lunch In(Column F)--Day Out(Column G)]
[Night In(Column H)--Night Out(Column I)]
What I want is for the formula to sum up time worked while accounting for possible data entry problems. Ex:
Day Out is blank when Lunch In has been filled in (thus giving me ""-[an actual time]).
The best I have been able to to is create a message saying that a time is missing or mistyped.
Instead, I'd rather have a running total for accurate data. Ex:
Lunch Out-Day In returns a positive time value when Day Out-Lunch In and Night Out-Night in don't.
The night time slots may be blank.
It is also possible that the night time slots will be the only slots filled in.
Here is the formula I'm using:
=IF(OR(C2="YES",C2=""),IF(AND(E2>=D2,G2>=F2,I2>=H2),(E2-D2)+(G2-F2)+(I2-H2),"Time missing or mistyped"),"OFF")
In the formula, C2 references a Data Validation List stating whether the day is a working day or a day off. If it's a working day, C2 will display YES.
Thanks,
tj
I'd also appreciate a simpler syntax to achieve the result I've got already.
There are 3 possible time slots defined by six Columns:
[Day In(Column D)--Lunch Out(Column E)]
[Lunch In(Column F)--Day Out(Column G)]
[Night In(Column H)--Night Out(Column I)]
What I want is for the formula to sum up time worked while accounting for possible data entry problems. Ex:
Day Out is blank when Lunch In has been filled in (thus giving me ""-[an actual time]).
The best I have been able to to is create a message saying that a time is missing or mistyped.
Instead, I'd rather have a running total for accurate data. Ex:
Lunch Out-Day In returns a positive time value when Day Out-Lunch In and Night Out-Night in don't.
The night time slots may be blank.
It is also possible that the night time slots will be the only slots filled in.
Here is the formula I'm using:
=IF(OR(C2="YES",C2=""),IF(AND(E2>=D2,G2>=F2,I2>=H2),(E2-D2)+(G2-F2)+(I2-H2),"Time missing or mistyped"),"OFF")
In the formula, C2 references a Data Validation List stating whether the day is a working day or a day off. If it's a working day, C2 will display YES.
Thanks,
tj
I'd also appreciate a simpler syntax to achieve the result I've got already.