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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Oh thank you so much!! That does it!!
-
No problem at all! 🙂
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!