Time Sheet Formula Help

%3CLINGO-SUB%20id%3D%22lingo-sub-1276604%22%20slang%3D%22en-US%22%3ETime%20Sheet%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1276604%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20my%20time%20card%20(I%20work%20for%20my%20mum)%20and%20am%20having%20some%20problems%20with%20the%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3ETotal%20Hrs%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E(Hours)%20%26amp%3B%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3EPay%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eformulas.%20Please%20direct%20your%20attention%20to%20those%20two%20columns%20on%20the%202nd%20week%20(starting%20March%2031)%20of%20the%20worksheet.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EWhen%20I%20am%20clocked%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CEM%3Ein%3C%2FEM%3E%2C%20it%20shows%20'%23%23%23%23%23%23'%20for%20the%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3ETotal%20Hrs%3C%2FSTRONG%3E.%20I%20would%20like%20for%20it%20to%20be%20blank%20when%20I%20am%20clocked%20in.%3C%2FLI%3E%3CLI%3EAlso%20when%20I%20am%20clocked%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CEM%3Ein%3C%2FEM%3E%2C%20it%20shows%20my%26nbsp%3B%3CSTRONG%3EPay%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eas%20some%20weird%2C%20random%20negative%20number.%20I%20would%20like%20for%20it%20to%20also%20be%20blank%20when%20I%20am%20clocked%20in.%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20should%20be%20obvious%2C%20but%2C%20for%20example%2C%20on%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3EC15%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3EI%20clocked%20in%20at%2010%3A30%20AM%2C%20and%20am%20currently%20on%20the%20clock%20writing%20this%20(ahaha).%20Therefore%2C%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3EF15%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eis%20showing%20'%23%23%23%23%23%23'%2C%20and%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3EH15%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eis%20showing%20%24-158%20for%20today.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPLEASE%20HELP!%20This%20is%20driving%20me%20absolutely%20insane%20and%20have%20never%20felt%20the%20need%20to%20reach%20out%20to%20a%20forum%20like%20this%20before.%20I%20always%20figure%20this%20kind%20of%20thing%20out.%20Thanks%20so%20much%20in%20advance%20for%20taking%20the%20time%20to%20look%20at%20this!!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1276604%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1276719%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20Sheet%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1276719%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F606915%22%20target%3D%22_blank%22%3E%40masonwood291%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESwitch%20the%20test%20to%20Time_Out%20(column%20D)%20rather%20than%20Time_In.%26nbsp%3B%20The%20test%20can%20be%20'not%20blank'%20as%20you%20have%20it%20or%20you%20can%20test%20for%20a%20positive%20number%20since%20that%20is%20how%20times%20after%20midnight%20are%20represented.%26nbsp%3B%20That%20should%20prevent%20the%20formula%20from%20calculating%20negative%20durations%20for%20the%20hours%20worked.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1277122%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20Sheet%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1277122%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20would%20that%20look%20like%3F%20I'm%20unfamiliar%20with%20this%20function%20and%20couldn't%20find%20much%20help%20on%20google%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

 

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 Replies
Highlighted

@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.

Highlighted

@Peter Bartholomew 

 

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