Hi,
I'm trying to make an editable sample rota for a business plan template and having some troubles getting time data to work how I want it to. I've tried googling it, but keep getting information on setting the format, which I have done.
The business is a bar, so I am trying to input times like 12:00 in E5, 01:30 in E6, and F5&6 are merged together with the following formula:
=IF(E6<E5,((E6-E5)+12),(E6-E5))
All these cells are formatted: custom - hh:mm
So end time minus start time to get hours, with a 12 hour adjustment where needed.
This works fine, and is repeated for each day of the week.
In column S, S5&6 merged together, I have a week total with formula:
=F5+H5+J5+L5+N5+P5+R5
This cell is formatted: custom - [h]:mm;@ - I use this as the above formatting would return 00:00 instead of 48:00.
Now I've established the problem, shown below, in this example I only have one days work on the rota:
Start 12:00 Cell E5 shows 12:00
End 01:30 Cell E6 shows 01:30
F5&6 shows 13:30
BUT in column S it shows 277:30
If instead of End 01:30 if I type "25:30", then it displays as 01:30, F5&6 display 13:30, but column S is correct, and also displays 277:30.
Even this seems odd, if the number were 181:30 then its added a week. (24*7+13.5) so I don't know where this comes from.
I need to share the document around and want to make it as intuitively usable as possible, so I'm keen to get rid of this, but the only way I can think of would be a giant table with lots of lookups.
Very clunky!
What is a more elegant solution? Is there better formatting I could use, or modify one of the formulae?
Thank you,
Bookmarks