Excel sum not working for hours

Question

I have a work schedule table with start time, end time, daily total:

The Daily Total function is as follows:

=IF(AND(WEEKDAY(A6,15)=6,WEEKDAY(A6,15)=7),"",IF(OR(B6="Sick",C6="Sick"),"8:00",C6-B6))

Basically what it says is:

If it's weekend don't place anything in the daily total cell.
If Start Time or End Time says Sick place 8:00 in the daily total cell.
Otherwise, calculate the time difference and place it in the Daily Total cell.

I assume that by now you know that I am making a simple working schedule excel spreadsheet with multiple conditions. So, the problem is that Excel cannot sum the Daily Total correct because of the function I had created for the Daily Total field.

Problem illustration:

If you have these hours inserted manually:

7:18
8:00

Then you sum them up you get 15:18. So far so good...

If you use my function to get these numbers and you try to sum them up you get 7:18, why is that? I reckon it has something to do with my function rather than some Excel settings but I could be wrong.

Thanks for the help.

Rory · Accepted Answer · 2015-07-27 07:45:25Z

3

Your function is entering text into the cell, which the SUM function will ignore. Use this instead:

=IF(AND(WEEKDAY(A6,15)=6,WEEKDAY(A6,15)=7),"",IF(OR(B6="Sick",C6="Sick"),TIME(8,0,0),C6-B6))

answered Jul 27, 2015 at 7:45

Rory

34.2k5 gold badges40 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

odedta Over a year ago

That never crossed my mind, thank you very much Rory.

odedta Over a year ago

Actually, now that I have updated my function accordingly I still don't get the desired resul. Could you possibly look at my table?

odedta Over a year ago

I honestly have no idea, I don't get the desired result when I'm trying this function on my entire table, when I try it in a different sheet with a few days the SUM is working just fine.

Rory Over a year ago

If it's just the display that's the issue, change the cell format for I4 to [h]:mm rather than h:mm

Collectives™ on Stack Overflow

Excel sum not working for hours

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related