Forum Discussion
Timesheet Help - Adding potential unpaid break
Hi all,
I'm trying to create a timesheet that calculates my hours while subtracting a potential unpaid break. Currently, what I have is =IFERROR(MOD(([@[Time Out]]-[@[Time In]])-([@[Unpaid Time End]]-[@[Unpaid Time Start]]),1)*24,0). This works, but only if values are inputted under the unpaid times. This is great if I do have an unpaid break. But what if I don't take a break? I'd have to enter the exact same time under unpaid time in and unpaid time end (e.g., 2:00pm - 2:00pm), or else the hours worked for that day will stay 0.
How do I rewrite the formula so that Unpaid Time Start/End is only calculated if time values are inputted? I really appreciate any help.
3 Replies
Try this:
=MOD([@[Time Out]]-[@[Time In]]-[@[Unpaid Time end]]+[@[Unpaid Time Start]],1)*24Leave the unpaid start and end blank if not applicable and custom format column I as General.
If you explicitly want to show that you didn't take a break enter zeroes, but don't leave the text "Unpaid Time ...." in columns F:G. A text will cause a #VALUE! error that IFERROR replaced with a 0 in your formula.
Riny_van_Eekelen, I'm grateful for your response! It looks like that formula isn't much different than my current formula. My original formula also would've worked if I kept the columns under Unpaid Time Start and Unpaid Time End blank. I was hoping there would be a way to keep the text in there, but if not, I'll delete it.
I greatly appreciate your time.
AmandaLPaull There is an alternative. With the proper custom number format, you can have the display of a number replaced by the display of specific text. See the attached workbook.
