1. Registered User
Join Date
Apr 2012
Posts
5

I have a excel sheet in which column A has dates,column B has criteria (either P1 OR P2) ,COLUMN C has time on, column D has time off, column E has total time.

Note: Day (06:00 till 22:00) NIGHT ( 22:00 till 06:00)

I would like to calculate the summary monthwise for day timing and night timings for P1 as well as P2.

2. Registered User
Join Date
Jan 2012
Posts
98
Hi,

Please find attached file with what I think you are looking for. In particular it is worth understanding the following features...

1. The fact that Excel stores a time as a proportion of the 24H day. So 06:00 would be 0.25 and 18:00 would be 0.75

2. The use of the MOD function to ensure that the value given for hours worked is not a negative value - MOD(number, divisor) = number - divisor *INT(number/divisor)

3. The setting of the hurdle whereby day shifts end and night shifts begin - in your case 22:00

3. Registered User
Join Date
Apr 2012
Posts
5

## Thanks

Dear Dave thanks a lot for the solution...But i am actually have to enter the time in hours and minutes. How to solve that one ?

4. Registered User
Join Date
Jan 2012
Posts
98
It will not make any difference. In all of the formulae simply remove anything that is multiplied by 24 as this will give you the whole number of hours. To display the number of hours and minutes you just need to format the cell as a time and it will display as such.

5. Registered User
Join Date
Apr 2012
Posts
5

## Thanks

Dear Dave,

I got the point.

As you have taken the range as \$C\$3 to \$C\$19, how shall i get the range with the month's name.
I mean i want the summary monthwise.

6. Registered User
Join Date
Jan 2012
Posts
98
=SUMPRODUCT(--(\$C\$3:\$C\$20="P1"),--(\$B\$3:\$B\$20>DATE(2012,12,1)),--(DATE(2012,12,31)>\$B\$3:\$B\$20),\$I\$3:\$I\$20)

You'll need to change the dates to give you the sub totals for each month.

7. Registered User
Join Date
Apr 2012
Posts
5

## Thanks

Dear Dave,

Thanks a lot for the help.
I have got the solution that after 22:00 it will be night.

Now the problem exists that night timing shall be from 22:00 till morning 07:00.

How shall i approach for the solution ?