Trying to create a formula to work out rostered hours

Originally Posted by Macka2626

have each cell set up to say start and finish times (e.g. 0700 - 1600)

It would be much easier if you put the times in separate cells:

A1 = 7:00
B1 = 16:00

Also, make sure you enter the time as a time value using the colon! Work with Excel, not against it.

hi I've just seen your post Tony Valko is right about using separate cells, you can if you wish try this try uploading a demo copy of your sheet.

if you want to use your present format then I would use helper cells with the following formulas

start and finish times in C5

D5 Format to [h]:00

Please Login or Register  to view this content.

E5 Format to General

Please Login or Register  to view this content.

F5 Format to General

Please Login or Register  to view this content.

G5 Format to General

Please Login or Register  to view this content.

Formula for Hourly breakdown AY5 copy across

Please Login or Register  to view this content.

here is an example Demo Hourly Breakdown.xlsx

I hate seeing a post go unsolved!

Can you post a SMALL sample file and show us what results you expect?

20 rows worth of data is plenty. We don't need (or want) to have to wade through 100's or 1000's of rows of data to test solutions.