Formula to recognise Saturday or Nightshift etc for timesheet

[kazmacdow]

Hi

I have attached an example workbook.

I am trying to get a formula for Column AB to recognize if Column B is either Saturday or Sunday then it should be zero.

Then Column AC needs to recognize if it is a Saturday or Sunday it needs to sum in that cell i.e. if they work Sat/Sun it should be paid as overtime.

Also if Column N shows more than 8 hours however it has 'nightshift' in column D it needs to sum in column AB for anything up to and incl 8 then anything above that should be summed in column AD.

Phew this is a tricky one!

Good Luck if you can help!!

Thanks heaps

[kazmacdow]

Oh it wont let me upload the file? it is 1.02mb is that too big?

[Punnam]

kazmacdow,

Yes it will not allow , the file should be less than 1 mb .

Punnam

[kazmacdow]

Oh! I am not sure how to make it smaller?

[protonLeah]

You need to ZIP the file. Locate the file in windows explorer, right click and select send to > compressed (zipped) folder. Then post the zipped file

[kazmacdow]

Ha! of course!

[Punnam]

kazmacdow

Update the expected results manually taking a particular date as an example

Punnam

[canapone]

Hi,

in column AB you coul add a segment to your formula

=IF(M709*24>=10,10,M709*24)*LOOKUP(G709,'Dowells Allowances'!$B$26:$B$31,'Dowells Allowances'!$C$26:$C$31)*(weekday(A709,2)<6)

it should return numbers excepting Saturdays and Sundays

Regards

[canapone]

Hi again

I hope to have understood

in column AC you integrate your formula adding

*(weekday(A709,2>5)

to isolate Sundays and Saturdays.

Regarding column N you could try a segment like

MIN(N709,8)

to sum up first 8 hours.

.....

Hope it's a little step forward

[kazmacdow]

Hi Canapone

Your formula has worked thank you for Column AB.

[kazmacdow]

Hi Canapone

The formula for AC doesn't seem to be working?

I am also abit lost with your column N suggestion? My formula basically needs to say if Column D says nightshift (Sheet1!A46) then anything great than 8 in Column N needs to be multiplied using the same formula LOOKUP(G6,'Dowells Allowances'!$B$26:$B$31,'Dowells Allowances'!$D$26:$D$31... I think!

I am so lost!

[canapone]

Hi

in column N maybe could help

Please Login or Register  to view this content.

I don't understand in your formula INT(M2) - my bad

Waiting for better solutions

Cheers