# 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

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

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

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

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1