Hour Computation

Hi guys, I'd like to do an hour computation like from 21:00 till 7:00 the following day

I've tried =TEXT(C4-C5,"h:mm") but instead of 10 hours I'm getting 14 hours. I've tried switching to am/pm but same result and if I switch c5 and c4 I get a value error....

Any ideas what I'm doing wrong or perhaps another formula that works.

Thanks in advance

Thanks John that works so far but only if C4 and C5 have data. If only one field is filled it gives incorrect hours

If only C4 is filled it counts the hours till midnight
If only C5 is filled it starts counting from midnight

Is there a possibility to get a value error otherwise the total hours could be incorrect. Also easier to see if I missed sth

Formula

Formula:

Please Login or Register  to view this content.

and time format

Hi guys, thought everything works so far and highlighted the days without work or faulty entries but when I summed it all up it only shows 0 value

Changed the format to h:mm,@ as suggested on various pages but no luck

Originally Posted by JohnTopley

Results formmated as TEXT: changed to format as time .

=IF(AND(C4<>"",C5<>""),(C5-C4+IF(C5<C4,1,0)),"")

format as h:mm

See attached

I've tried all different formats in between hoping sth would work but had h:mm in between as well....

It gives a summary but my total is incorrect. It shows 16 hours only instead of 40

Unfortunately also my cell rules don't work anymore with the new formula....

Originally Posted by popipipo

Please Login or Register  to view this content.

Or this alternitive.

Thanks Popipipo but unfortunately the same problems....total doesn't add up for whatever reason and also my conditional formatting doesn't seem to work anymore...

Awesome that works, thanks a lot _/\_

Any ideas why my conditional formatting doesn't work highlight the cells a before? In one of my previous files it worked with cell value less than =",01" to be marked as red. Tried also zero value but same outcome. I would like to showcase days without work as well as when there are faulty entries like only starting or end time

May have to change the value to a bit lower as I've seen with a different format that 8 hours is only 0.3333 of a day but just doesn't work in general anymore

The formatting seems to be correct now since the hours and the total appear correct, yet my conditional formatting still fails.

Have two conditional formatting as per screenshot but neither of it works....