# Sum of hours and minutes over 1000hrs and converting to week days

1. ## Sum of hours and minutes over 1000hrs and converting to week days

Hi,
Please help to get a appropriate formula to sum below hours, I am getting the total as 537:11:27 (shown in A6) if I use =sum(A1:A5),
• Need formula to sum these hours
• Convert to weekdays considering only Mon-Fri (5days in a week), expecting in this format 5w 2d

Provide these two formula by considering that i am going to use in two cells.

 Column h:mm A1 1101:58 A2 2.59 A3 2003:11 A4 19.41 A5 0.39 A6 (Total) 537:11:27  Register To Reply

2. ## Re: Sum of hours and minutes over 1000hrs and converting to week days

One more request, please consider 8hrs=1day when converting to week/day  Register To Reply

3. ## Re: Sum of hours and minutes over 1000hrs and converting to week days

Please post a sample sheet so we can see real formatting  Register To Reply

4. ## Re: Sum of hours and minutes over 1000hrs and converting to week days  Register To Reply

5. ## Re: Sum of hours and minutes over 1000hrs and converting to week days

Hi Chinto..

Can you please check by changing below two formula..

check with Below formula..
Formula:  `Please Login or Register  to view this content.`

and in E9, instead of =INT(((Calculation!N24+Calculation!N48)*0.15)/60)&":"&MOD(((Calculation!N24+Calculation!N48)*0.15),60) this formula check with below..
Formula:  `Please Login or Register  to view this content.`

Format the cell as required..

Now.. hope your SUM formula is working..

Above both formula returning TEXT.. not possible to use in SUM  Register To Reply

6. ## Re: Sum of hours and minutes over 1000hrs and converting to week days

Hi Debraj Roy,thanks for the response...

I am still having some issue. E7 is showing 62.103 which I feel is incorrect, I am expecting in h:mm format, also it does not sum all the rows, see below image.

It will be great if you could test in the attached template and provide an solution, thanks

image.JPG  Register To Reply