Time formats and time data problem.

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,

Your first formula is incorrect, as you are effectively adding 12 days rather than 12 hours (times are stored internally by Excel as fractions of a 24-hour day). It needs to be:

=IF(E6<E5,((E6-E5)+0.5),(E6-E5))

although you don't really need so many brackets. There are other ways that this formula could be written, as well.

Hope this helps.

Pete

Thank you for the quick reply,

Apologies for the many brackets - I am pretty much entirely self taught on excel so I am sure I have numerous bad habits.

Unfortunately that doesn't work:

Changing F5&6 to:

=IF(E6<E5,((E6-E5)+0.5),(E6-E5))

start 12:00
end 01:30

column F returns: 01:30 not 13:30

start 12:00
end 25:30 (displays 01:30)

column F returns correctly: 13:30

If I change:
Start 14:00
end 25:30 (displays 01:30)

column F returns correctly: 13:30

But if I change:
Start 14:00
End 01:30

Column F returns: ###############################################

In all scenarios Column S returns exactly as Column F.

So, it has somewhat fixed the problem- column S gives no discrepancies to column F, but column F no longer shows correctly.

=if(e5>e6,e6-e5+1,e6-e5)

Originally Posted by Jamesera27

=if(e5>e6,e6-e5+1,e6-e5)

Thanks! That seems to work. Typing both 01:30 or 25:30 both display as 01:30 and the correct hours show in both Column F and Column S!
Great...

Any chance you can explain why it works? What is the "+1" adding, how should I have been able to know that? Does it add a number of hours, or days? I'm keen to learn as well as fix problems here...

Thank you