Time Sheet Formula Help

[masonwood291]

I have attached my time card (I work for my mum) and am having some problems with the Total Hrs (Hours) & Pay formulas. Please direct your attention to those two columns on the 2nd week (starting March 31) of the worksheet. 

• When I am clocked in, it shows '######' for the Total Hrs. I would like for it to be blank when I am clocked in.
• Also when I am clocked in, it shows my Pay as some weird, random negative number. I would like for it to also be blank when I am clocked in.

This should be obvious, but, for example, on C15 I clocked in at 10:30 AM, and am currently on the clock writing this (ahaha). Therefore, F15 is showing '######', and H15 is showing $-158 for today. 

PLEASE HELP! This is driving me absolutely insane and have never felt the need to reach out to a forum like this before. I always figure this kind of thing out. Thanks so much in advance for taking the time to look at this!!!

[#2]

@masonwood291 

Switch the test to Time_Out (column D) rather than Time_In.  The test can be 'not blank' as you have it or you can test for a positive number since that is how times after midnight are represented.  That should prevent the formula from calculating negative durations for the hours worked.

[#3]

@Peter Bartholomew 

What would that look like? I'm unfamiliar with this function and couldn't find much help on google