Adding negative numbers when the cell is blank
I have a formula to add different columns of clocking in and out times. I have it set up to show the Clock In times as negative and Clock Out times as positive in order to get the total for the day. However if the lunch time below are blank I get an #invalid operation. Guessing because the Lunch time in has the negative in front?
=[Start Day  Time In Total Hours]@row + [Lunch  Time Out Total Hours]@row + [Lunch  Time In Total Hours]@row + [End Day  Time Out Total Hours]@row
Any ideas on how to add when lunch times are blank?
Thank you!!
Best Answer

You can add IFERROR statements around your two negative functions so that if there's an error it returns 0 instead of 0
Try this:
=IFERROR([Start Day  Time In Total Hours]@row, 0) + [Lunch  Time Out Total Hours]@row + IFERROR([Lunch  Time In Total Hours]@row, 0) + [End Day  Time Out Total Hours]@row
Cheers,
Genevieve
Answers

You can add IFERROR statements around your two negative functions so that if there's an error it returns 0 instead of 0
Try this:
=IFERROR([Start Day  Time In Total Hours]@row, 0) + [Lunch  Time Out Total Hours]@row + IFERROR([Lunch  Time In Total Hours]@row, 0) + [End Day  Time Out Total Hours]@row
Cheers,
Genevieve

Oh thank you so much!! That does it!!

No problem at all! 🙂
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.5K Get Help
 61 Global Discussions
 46 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 124 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!