SOLVED

Formula does not work in my computer

Copper Contributor

Date..............Day...............Time In............Time Out...........Time In............Time Out...........Total Hours
5/24/2022.....Tuesday.......08:05AM........12:00PM...........07:00PM .......12:01 AM ...........#VALUE!

I used this =(E9-D9)+(H9-G9) Formula. But my computer didn't work. I Go to Region-Additional Date & Time- number-list operator. But didn't work formula.

PROBLEM.JPG

6 Replies

@sharear 

The time in D9 is PM and that in E9 is AM, so the checkout time is after midnight. This makes the difference E9-D9 negative. Excel cannot handle that.

Use the following:

 

=MOD(E9-D9,1)+MOD(H9-G9,1)

 

Format I9 as h:mm. For Total Hours in I9, you will want to use the custom format [h]:mm to allow for a total over 24 hours,

Dear @Hans Vogelaar, I place your formula. but doesn't work this formula.

problem-2.JPG 

@sharear 

Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.

best response confirmed by Hans Vogelaar (MVP)
Solution

@sharear 

Are you sure you have time formatted in am/pm time format, not texts? In first case here

image.png

it shall be space before PM. You may check applying General format to that cell. If time, you'll see the number ~0.6674

THANKS SIR.

@sharear , you are welcome

1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@sharear 

Are you sure you have time formatted in am/pm time format, not texts? In first case here

image.png

it shall be space before PM. You may check applying General format to that cell. If time, you'll see the number ~0.6674

View solution in original post