Numbers time duration formula

Can you provide more info? I can't visualize your table to be able to work out what you're trying to do.

You say you have 7 columns, one per day. It isn't clear what values are in those columns, or why TIMEVALUE() would be appropriate.

It also isn't clear where this formula is going. You say you're referring to columns D and C, but where is this formula? You only have 7 columns (one per day).

Also, TIMEVALUE() takes a date string and returns a fraction of day. It can't take a column input

So, in all, I can't see what you're working with, and I can't understand what you're trying to achieve. More detail may help. A screenshot of your worksheet might be a good starting point.

Without a screenshot it's hard to tell exactly what you are doing but if you haven't do so already you might have a look at the E'mployee Schedule' template (at File > New).

In the 'Sign In' table it has a formula that uses ISBLANK to handle cases where there is not both a time In and a time Out.

SG

That's a very complex spreadsheet, and no way I (or anyone?) could have discerned your ask from the original post.

It's also very Excel-esque in putting the calculations off screen in columns Z+. Nothing inherently wrong with that, but a Numbers' way would be to use a separate table. Either way.

I'll assume for now that you have the basic logic down in terms of calculating hours, so I'm not going to re-invent the wheel. I think your easiest approach is to just wrap the existing formulas in an IFERROR(). This will catch errors and may be sufficient.

So, instead of:

=24*(TIMEVALUE(D)-TIMEVALUE(C)+IF(TIMEVALUE(D)<TIMEVALUE(C),1,0))

say something like:

=IFERROR(24*(TIMEVALUE(D)-TIMEVALUE(C)+IF(TIMEVALUE(D)<TIMEVALUE(C),1,0)),0)

This runs the exact same function you have, but if a non-time value in the cell triggers an error then IFERROR() catches it and returns 0

I'm still confused by your formulas that seem to reference entire columns - e.g. TIMEVALUE(D) - rather than specific cells such as TIMEVALUE(D7), but if it's working I'm not going to touch that :)