Summing nested IF statements gives strange answer

Topic

New Reply

Excel 2010:

I have 14 rows that contain this IF statement relative to the row:=IF(C8=Holidays!$D$6,”9:00″,IF(C8=Holidays!$D$10,”9:00″,IF(C8=Holidays!$D$11,”9:00″,IF(C8=Holidays!$D$12,”9:00″,IF(C8=Holidays!$D$15,”9:00″,IF(C8=Holidays!$D$16,”9:00″,IF(C8=Holidays!$D$17,”9:00″,IF(C8=Holidays!$D$18,”9:00″,” “))))))))

If the dates in column C are equal to the dates in the table Holidays, I want 9 hours inserted into cells in column N. Then I have this formula =SUM(N8:N22) to calculate the number of total “holiday hours” in cell J 30

My problem is, the sum is way off. for example if two of the cells get 9:00 hours inserted the sum will be 432. Why does adding 9 + 9 give me 432?
37144-timesheet

Reply | Quote

Replies

It would be helpful to see the formulas and results, could you attach a sample workbook?

Steve

Reply | Quote

In working with this, I am finding it has something to do with using the h:mm format. If I change the formatting to numbers, all is good.

Reply | Quote

I don’t see a way to insert/attach a file.

Reply | Quote

Rather than using Quick Reply, press the Go Advanced button, and you will see a button that allows you to attach a file.

Reply | Quote

Does [h]:mm format make more sense? It is elapsed time and will go over 24 hrs. h:mm is for time of day and will only be 0:00-23:59.

To upload file see: http://windowssecrets.com/forums/showthread//162379-Maintenance-shedule-based-on-completion-dates?p=955184&viewfull=1#post955184

Steve

Reply | Quote