Excel Time formatting for multiple times in a single cell

Good Day all,

I have a question I'm sure there is a fairly simple solution to. Basically I want to take a time IE 07:15 and in the next cell over I want to show the "time bank" that that time falls into. For example, 07:15 would fall into the 0700-0800 "time bank". etc etc. Right now I'm using a formula that looks like this:

Please Login or Register  to view this content.

where B4 contains the time. Most of the time I get the result I'm looking for, IE if the Time is 21:15 then the formula result will be 2100-2200, which is exactly right.

Unfortunately when it calculates a time under 1000, IE 07:15, I get the result: 0700-800, instead of 0700-0800. Is there a way to make sure that its always 4 digits for each portion of the "time bank"?

Is there a better formula to use to get the result I'm looking for?

Any assistance would be greatly appreciated.

Chris

Hello,

I have taken a look at this & amended the formula you have been using & it seems work ok, I tried several examples for times.

Please Login or Register  to view this content.

I have removed the +1 & added into the second TEXT formula the value of "B4+0.05" which will add 1 hour to the B4.

Hope this helps.

Best Regards

Jamesr571

P.S: if this has helped resolve the query please click on the star of this reply.

Thank you both for the replies. Both work perfectly.

Chris