Sum of 09:00 - 17:00 etc

Hi

Apologies as I'm sure this is an easy solution:

I'm trying to calculate the amount of hours worked in the following ranges. for example

A1 = 09:00 - 17:00 (8h)
B1 = 08:00 - 15:00 (7h)
C1 = 08:00 - 14:00 (6h)
D1 = 08:00 - 18:00 (10h)
E1 = 17:00 - 21:00 (4h)
F1 = 22:00 - 08:00 (10h)
G1 = 08:00 - 15:00 (7h)

How would I add a formula that displays the resulting hours from all the range? So that the cell containing the formula would display 50.

Plenty of trial and error and googling.. I'm sure I'm missing something.

Cheers

Or
=SUM(A1:G1)*24
Format the cell as a Number, not time.

Sounds like the values in A1:G1 are not really time values, just text strings.

Can you post a sample book (an XL Book, not a picture)?
Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Yes they're text values sorry, essentially needing the value assigned to them. I can't upload at work currently. Will do ASAP.

The shifts (eg 09:00 - 17:00) are just taken from a table and populated with data validation pulled from VLOOKUP.

Is there a way to assign a value to each text string(e.g. 09:00 - 17:00 = 8) easily?

Will upload when I can tomorrow AM.

Thanks.

Can you give some examples of what 'Exactly' is in the cells?

"09:00 - 17:00"
"08:00 - 15:00"
"08:00 - 14:00"

And so on.

Thanks for the patience, sorry.

Try

=SUMPRODUCT(MOD((1+RIGHT("00000"&A1:G1,5))-LEFT(A1:G1&"00000",5),1))
Formatted as time [hh]:mm

or add *24 to the end and format as a number.

Another approach

No luck. Returns as #VALUE! even when formatting the cell.

See attached - to SUM I7:O7 & show the value in AK.

Hello
A little convoluted but this seems to work:

Formula:

Please Login or Register  to view this content.

Although using SUMPRODUCT the use of the IF function means this must be entered as an array with Ctrl+Shift+Enter. If correctly entered you will see the curly brackets {} around the formula. Format the formula cell: [h]:mm.

DBY

Brilliant! Thank you ever so much.