Problem with "IF(OR" within my time sheet.(or perhaps its just too early)

[galvinpaddy]

Good morning happy campers,

Attached - is a time sheet i have come up with (only August has data in for now)
And up until i added in more than 8 hours on the weekend it was working great.
The sheet accurately records the overtime worked on a weekday but i cant understand how to calculate the formula for weekends.

Ideally i would like the formula to be the same in all Column J, as i will only be pasting the weekdays into Column A.
Would really appreciate it if someone could have a look and show me where im going wrong, or show me how to improve/simplify it.

Kind regards, galvinpaddy.

[galvinpaddy]

Ok, i think i sorted it, the formula in J3 for example.

Please Login or Register  to view this content.

When i changed it to read

Please Login or Register  to view this content.

It appears to work
Time to test!

---------- Post added at 12:51 PM ---------- Previous post was at 11:50 AM ----------

ok, so i return with another issue,

If i change my clock in time to 08:00 & my clock out time to 16:30, it produces a weird number format in J (ignore Column I, can be deleted)
It simply shows up as 1.33227E-15
Any idea why?

regards.

[martindwilson]

its a precision thing although f3=j1 =true
(f3-j1)*24 = 0.0000000000000013322676
not zero
try rounding
=IF(C3="","",ROUND(IF(OR(A3="Sat",A3="Sun"),G3-H3,F3-$J$1)*24,2))
alsoyou cant multiply a number by blank or you'll get value error you may want to change
=IF(C3="","",IF(F3=$J$1,"",F3-$J$1)) to
=IF(C3="","",IF(F3=$J$1,0,F3-$J$1))

[galvinpaddy]

Hi & thanks for your reply.
When i add your formula the hours dont seem to calculate properly?
If i add your formula, then the hours for Sat & Sun remain the same regardless of start finish time??

[martindwilson]

which formula are you talking about? why have you got all those helper columns? another formula that looks wrong is
=IF(F6="","",IF(A6="Sat",G6-H6,H6*24)) why isn't that =IF(F6="","",IF(A6="Sat",G6-H6,H6)*24)

[galvinpaddy]

Hi & Thanks for your reply!
(as in #2 post, Column I can be ignored / deleted)

The formula you gave me -

Please Login or Register  to view this content.

is the one that appears to cause an issue for me. if i use that formula in J3, and fill down, all times calculate accurately apart from the O.T (colum I) for Sat & Sun, regardless of what clock in/out time i enter into Columns B & C.

Columns B, C, G & H are all time format, D, E & I are all number format (so i can add up the figures shown in K3 & L3.) as i couldnt figure out how to do it without converting time to numbers.

So far the only way i can get the overtime figure in the 'O.T' column for weekends to show correctly is to change the formula
From -

Please Login or Register  to view this content.

To -

Please Login or Register  to view this content.

But i cant figure out why, and ideally i would like to not have to modify the formula in any cell, to make it easier when adding times.

Thanks for your time so far!
galvinpaddy (updated sheet added with the irrelevant column removed and you formula added into 'O.T' (I), and

Please Login or Register  to view this content.

added into 'OT' (H3) which shows a value error.)

[ratcat]

Try this at H3

Please Login or Register  to view this content.

[galvinpaddy]

Hi, thanks for that, now my issue is getting the Sat & Sun hours to show accurately.
During the week i need the sheet to show how many hours over my working day are overtime, and on weekends the whole days is OT

regards, galvinpaddy