Calculating Allocated Hours Based On Clock In and Clock Out

[rahul_ferns76]

Hello Experts
I have an excel sheet in which depending on the clock in and clock out
excel calculates the allocated time. Default allocated time is 4 hours

So if a person does an 8 hour shift, the allocated hours should show 4 hours
if a person does an 9 hour shift, the allocated hours should show 4 hours

and if the person doesn't clock in or out, the allocated hours should show zero

I have attached screen shot and the excel file

Please help

Kind Regards
Rahul

[JohnTopley]

allocated hours (no column in your file)

=IF(worked Hours=0,0,4) ??


in C8

=IF(D8=0,0,4)

[FDibbins]

I think you are making things far more complex than you need to.

1. Instead if 1 sheet per day, why not put all data into 1 sheet and run your summaries from that? Rearrange the input so you also include a date
2. Instead of all those INDIRECT()'s (if you dont do (1), do the calcs on each sheet in a helper cell, then just reference that, instead of...
=((INDIRECT(L8&"'!$I$17")-INDIRECT(L8&"'!$I$13")+(INDIRECT(L8&"'!$I$17") < INDIRECT(L8&"'!$I$13")))-(INDIRECT(L8&"'!$J$15")-INDIRECT(L8&"'!$I$15")+(INDIRECT(L8&"'!$J$15") < INDIRECT(L8&"'!I15"))))*24
3. For your actual question, maybe something like...
=IF(TIMEEND-TIMESTART > 4/24,4/24,0)

[FDibbins]

I think you are making things far more complex than you need to.

1. Instead if 1 sheet per day, why not put all data into 1 sheet and run your summaries from that? Rearrange the input so you also include a date
2. Instead of all those INDIRECT()'s (if you dont do (1), do the calcs on each sheet in a helper cell, then just reference that, instead of...
=((INDIRECT(L8&"'!$I$17")-INDIRECT(L8&"'!$I$13")+(INDIRECT(L8&"'!$I$17") < INDIRECT(L8&"'!$I$13")))-(INDIRECT(L8&"'!$J$15")-INDIRECT(L8&"'!$I$15")+(INDIRECT(L8&"'!$J$15") < INDIRECT(L8&"'!I15"))))*24
3. For your actual question, maybe something like...
=IF(TIMEEND-TIMESTART > 4/24,4/24,0)

[rahul_ferns76]

Hi John
I just get a zero

I don't know what I am doing wrong

Regards
Rahul

[rahul_ferns76]

Hi FDibbins
Yes I think I have made it more complicated than what it needs to be
I don't know how to simplify it

I tried the =IF(TIMEEND-TIMESTART > 4/24,4/24,0)

I get a zero

Regards
Rahul

[JohnTopley]

Rahul,
Can you post a file showing "Allocated hours" column.

I can't think why this doesn't work

=IF(D8=0,0,4)

as it simply checks if "Worked Hours" in D is zero, then "Allocated Hours" is 0, otherwise it is 4.

[rahul_ferns76]

Hi John
I have attached my excel file

I added a column Allocated hours, but it wont work

[JohnTopley]

Use

=IF(D8=0,0,4/24)

[rahul_ferns76]

Hi John
It works

Thanks a lot

Regards
Rahul

[rahul_ferns76]

Hi John
It works

Thanks a lot