SOLVED

formula help

%3CLINGO-SUB%20id%3D%22lingo-sub-1769662%22%20slang%3D%22en-US%22%3Eformula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1769662%22%20slang%3D%22en-US%22%3E%3CP%3ENeed%20some%20help%20with%20a%20formula.%26nbsp%3B%20As%20I%20enter%20in%20my%20beginning%20time%20of%208%3A00%20my%20total%20automatically%20enters%2016%3A00%20without%20entering%20anything%20in%20my%20end%20time.%26nbsp%3B%20%26nbsp%3BWhat%20formula%20%2F%20function%20do%20i%20need%20to%20wait%20for%20my%20total%20to%20add%20up%20after%20i%20enter%20in%20my%20end%20time%3F%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20formula%20in%20there%20now%20so%20if%20anyone%20can%20adjust%20that%20for%20me%20that%20would%20be%20great.%26nbsp%3B%20i%20also%20attached%20an%20example%20so%20you%20can%20better%20see%20what%20im%20talking%20about.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20time%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1769662%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-1769683%22%20slang%3D%22en-US%22%3ERe%3A%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1769683%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F289214%22%20target%3D%22_blank%22%3E%40spalmer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThanks%20for%20posting%20a%20sample%20workbook.%20Try%20this%20formula%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(OR(I6%3D%22%22%2CL6%3D%22%22)%2C%22%22%2CMOD(L6-I6%2C1))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1772461%22%20slang%3D%22en-US%22%3ERe%3A%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1772461%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3Bthank%20you%20very%20much%20Hans.%26nbsp%3B%20it%20worked.%26nbsp%3B%20if%20you%20can%20and%20if%20you%20have%20time%20could%20you%20explain%20that%20formula%20so%20i%20can%20learn%20from%20it%3F%26nbsp%3B%20if%20not%20no%20big%20deal%2C%20i%20appreciate%20your%20time%20already.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1772956%22%20slang%3D%22en-US%22%3ERe%3A%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1772956%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F289214%22%20target%3D%22_blank%22%3E%40spalmer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOR(I6%3D%22%22%2CL6%3D%22%22)%20is%20TRUE%20if%20either%20I6%20or%20L6%20is%20empty%20(or%20both%20are).%20So%20the%20formula%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(OR(I6%3D%22%22%2CL6%3D%22%22)%2C%22%22%2CMOD(L6-I6%2C1))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ereturns%20a%20blank%20if%20either%20cell%20is%20blank.%20If%20both%20cells%20are%20filled%2C%20it%20returns%20MOD(L6-I6%2C1).%3C%2FP%3E%0A%3CP%3EThis%20is%20the%20difference%20of%20L6%20and%20I6%20(i.e.%20hours%20worked)%2C%20but%20if%20L3%20is%20smaller%20than%20I6%2C%20it%20adds%201.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

Need some help with a formula.  As I enter in my beginning time of 8:00 my total automatically enters 16:00 without entering anything in my end time.   What formula / function do i need to wait for my total to add up after i enter in my end time?  

 

I have a formula in there now so if anyone can adjust that for me that would be great.  i also attached an example so you can better see what im talking about.

 

Thanks for your time

4 Replies
Highlighted
Best Response confirmed by spalmer (Contributor)
Solution

@spalmer 

Thanks for posting a sample workbook. Try this formula:

 

=IF(OR(I6="",L6=""),"",MOD(L6-I6,1))

Highlighted

@Hans Vogelaar thank you very much Hans.  it worked.  if you can and if you have time could you explain that formula so i can learn from it?  if not no big deal, i appreciate your time already.

Highlighted

@spalmer 

OR(I6="",L6="") is TRUE if either I6 or L6 is empty (or both are). So the formula

 

=IF(OR(I6="",L6=""),"",MOD(L6-I6,1))

 

returns a blank if either cell is blank. If both cells are filled, it returns MOD(L6-I6,1).

This is the difference of L6 and I6 (i.e. hours worked), but if L3 is smaller than I6, it adds 1.

@hans Thank you very much.  wasn't sure what MOD did.