CODEWITHSUNDEEP
excelvba
odedta
odedta

Reputation: 2478

Excel sum not working for hours

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:

  1. If it's weekend don't place anything in the daily total cell.
  2. If Start Time or End Time says Sick place 8:00 in the daily total cell.
  3. 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.

Upvotes: 0

Views: 876

Answers (1)

Rory
Rory

Reputation: 34085

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))

Upvotes: 3

Related Questions