Timesheet excel formula

[Louise1111]

Hi,
Trying to work out a spreadsheet as a timesheet.


I need it to be (If over 6 hours per day - 0.5 break)
As its two formula in one cell, I am getting slightly confused.
Need M5, over 6 - 0.5
Need N5, over 6 - 0.5 etc.

I already have a sum in the cells

=(C5-B5)*24 = (If over 6 hours per day - 0.5 break)

Any ideas please - tried a few ways.
Thanks
Louise

 

[JamesCanale]

So if you are over 6 hours a day, subtract half an hour, if not, just the full amount...

=if((c5-b5)*24>6,(c5-b5)*24-.5,(c5-B5)*24)

 

[R2ah1ze1l]

For a single day:

=IF(AND(RIGHT(B5,2)="AM",RIGHT(C5,2)="PM"),IF(((LEFT(C5,2)+12)-(LEFT(B5,2)))>6,((LEFT(C5,2)+12)-(LEFT(B5,2)))-0.5,((LEFT(C5,2)+12)-(LEFT(B5,2)))))

click and drag the formula for each of the remaining days.

I had to format B and C for the time, but I kept the cell with the formula general.

 

[Louise1111]

For a single day:

=IF(AND(RIGHT(B5,2)="AM",RIGHT(C5,2)="PM"),IF(((LEFT(C5,2)+12)-(LEFT(B5,2)))>6,((LEFT(C5,2)+12)-(LEFT(B5,2)))-0.5,((LEFT(C5,2)+12)-(LEFT(B5,2)))))

click and drag the formula for each of the remaining days.

I had to format B and C for the time, but I kept the cell with the formula general.

Thank you, but I cant understand what you mean?
Sorry I am finding it slightly confusing.


IN cell M5 I have
=(C5-B5)*24

In Cell R5 I have
=M5+N5+O5+P5+Q5


I was ideally looking to change the formula's just for
M5, N5, O5, P5, Q5
Then again for same letters but for row below.