Time to time range formula???

[Guest]

=TEXT(INT(C2*24)/24,"hh:mm") & "-" & TEXT(INT(C2*24+1)/24,"hh:mm")

is the formula I'm using. In my spreadsheet, column C lists the time the
patient was discharged. I was given the formula above last year from a
poster on an Excel website. I was looking at it today and I don't think it
is allowing for those that show up as 7:00 for example. It displays
06:00-07:00 and 07:00-08:00. Which one is it going in? Is there a better
formula that will put the times in the correct range?

 

[Guest]

it goes in 07:00-08:00 on my testing.

 

[Sandy Mann]

I would use simply:

=TEXT(C2,"hh:mm")&" - "&TEXT(C2+"1:00","hh:mm")

or

=TEXT(C2,"hh:mm")&" - "&TEXT(C2+1/24,"hh:mm")


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

Thanks for the lightning fast reply Toppers!!! Is there a formula that will
display it as....say 06:00-06:59 07:00-07:59 and so on? The time display
is throwing off our CFO. I guess I could just change the display myself, but
I wanted to make sure it is where it needs to be in the event he wants proof.

 

[Guest]

that is more along the lines of what I was wanting, but need it to fit into a
range of whole hours, the formulas below gives the hour range from the time I
have in C2. Ex. 04:21 in C2, displays 04:21-05:21

 

[Guest]

that is more along the lines of what I was wanting, but need it to fit into a
range of whole hours, the formulas below gives the hour range from the time I
have in C2. Ex. 04:21 in C2, displays 04:21-05:21. I would need it to go
into 04:00-04:59.

 

[Sandy Mann]

=TEXT(CEILING(C2,"1:00"),"hh:mm")&" -
"&TEXT(CEILING(C2,"1:00")+"1:00","hh:mm")

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

oh....almost.... 19:05 is displayed as 20:00-20:59 when I changed the last
"1:00" to "00:59"...so is 1 hour ahead now

 

[Guest]

try:

=TEXT(TIME(HOUR(C2),0,0),"hh:mm")&" - "&TEXT(TIME(HOUR(C2),59,0),"hh:mm")

 

[Sandy Mann]

oh....almost.... 19:05 is displayed as 20:00-20:59 when I changed the
last
"1:00" to "00:59"...so is 1 hour ahead now

mmmmm......

Sound like a rounding error. It works for me so I cannot test it but try:

=TEXT(CEILING(C2,"1:00"),"hh:mm")&" -
"&TEXT(CEILING(CEILING(C2,"1:00")+"1:00","1:00"),"hh:mm")

or

=TEXT(CEILING(C2,"1:00"),"hh:mm")&" -
"&TEXT(CEILING(C2,"1:00")+1/24,"hh:mm")

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

You're a GENIOUS Toppers!!! Thanks so much!!!

 

[Guest]

Thanks Sandy for all your help, Toppers gave me the solution in his last
post, and worked perfectly.... Thanks again!!!