Payroll Sunday hour calculation from in and out times

[juhatalonen]

Hello,


I have been looking for a solution from this forum but I havent found anything proper.

I am having some troubles with calculating sunday hours from in and out times.

Basically what I am looking for is something like this.

EXAMPLE:
[TABLE="width: 500"]
<tbody>[TR]
[TD]IN[/TD]
[TD]OUT[/TD]
[TD]SUNDAY HOURS[/TD]
[/TR]
[TR]
[TD]23.3.2014 09:00[/TD]
[TD]23.3.2014 21:00[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]23.3.2014 21:00[/TD]
[TD]24.3.2014 09:00[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]


Thank you all if You are able to help me.

Best Regards,
Juha Talonen

 

[ace19852]

Ist change the "." to "/" in all the dates i.e 23.3.2014 09:00 should read 23/3/2014 09:00

Then in C2 enter =TEXT(B2-A2,"h:mm") and copy down

By the way in both your examples there is 12 hours difference between the 2 times

Cheers

 

[billszysz]

Hi,
Try this simple formula

=IF(WEEKDAY(A2)=1,IF(WEEKDAY(B2)=1,B2-A2,1-MOD(A2,1)),IF(WEEKDAY(B2)=1,MOD(B2,1),0))

Sunday is 1 day of week (you can change this if you want)

Greetings