I need help getting my formula to return the time in standard time (not
military time) In cell B5 I have =NOW() formula, formated to return
data like "August 18, 2006" and in cell B6 I have a formula to get the
time from B6 and round it up to the nearest 1/2hr. The formula I have
in B6 is =CONCATENATE(TEXT(B5,"dddddd"),"
",TEXT(ROUND(B5*48,0)/48,"H:MM")," ",TEXT(B5,"AM/PM")) and is formated
to retur data like "Friday 9:30 AM" But the only problem is that after
12:00pm it's returning military time, so my other formulas wont work,
cause I'm using a Vlookup that's looking up a Day and time in standard
time, i.e. "Friday 1:30 PM" rather than "Friday 13:30 PM" If anyone can
help me it would be greatly appreciated. Thanks.

=TEXT(ROUND(B6*48,0)/48,"dddd H:MM AM/PM")

HTH,
Bernie
MS Excel MVP


"GTVT06" wrote in message
oups.com...
I need help getting my formula to return the time in standard time (not
military time) In cell B5 I have =NOW() formula, formated to return
data like "August 18, 2006" and in cell B6 I have a formula to get the
time from B6 and round it up to the nearest 1/2hr. The formula I have
in B6 is =CONCATENATE(TEXT(B5,"dddddd"),"
",TEXT(ROUND(B5*48,0)/48,"H:MM")," ",TEXT(B5,"AM/PM")) and is formated
to retur data like "Friday 9:30 AM" But the only problem is that after
12:00pm it's returning military time, so my other formulas wont work,
cause I'm using a Vlookup that's looking up a Day and time in standard
time, i.e. "Friday 1:30 PM" rather than "Friday 13:30 PM" If anyone can
help me it would be greatly appreciated. Thanks.

Of course, I should have posted

=TEXT(ROUND(B5*48,0)/48,"dddd H:MM AM/PM")

instead of using B6....

Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
=TEXT(ROUND(B6*48,0)/48,"dddd H:MM AM/PM")

HTH,
Bernie
MS Excel MVP


"GTVT06" wrote in message
oups.com...
I need help getting my formula to return the time in standard time (not
military time) In cell B5 I have =NOW() formula, formated to return
data like "August 18, 2006" and in cell B6 I have a formula to get the
time from B6 and round it up to the nearest 1/2hr. The formula I have
in B6 is =CONCATENATE(TEXT(B5,"dddddd"),"
",TEXT(ROUND(B5*48,0)/48,"H:MM")," ",TEXT(B5,"AM/PM")) and is formated
to retur data like "Friday 9:30 AM" But the only problem is that after
12:00pm it's returning military time, so my other formulas wont work,
cause I'm using a Vlookup that's looking up a Day and time in standard
time, i.e. "Friday 1:30 PM" rather than "Friday 13:30 PM" If anyone can
help me it would be greatly appreciated. Thanks.

And I should have noted that you could have the formula

=ROUND(B5*48,0)/48

and apply a custom format of
dddd H:MM AM/PM

to the cell to achieve the same effect, but leave the cell as a date/time.

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
=TEXT(ROUND(B6*48,0)/48 HTH,
Bernie
MS Excel MVP


"GTVT06" wrote in message
oups.com...
I need help getting my formula to return the time in standard time (not
military time) In cell B5 I have =NOW() formula, formated to return
data like "August 18, 2006" and in cell B6 I have a formula to get the
time from B6 and round it up to the nearest 1/2hr. The formula I have
in B6 is =CONCATENATE(TEXT(B5,"dddddd"),"
",TEXT(ROUND(B5*48,0)/48,"H:MM")," ",TEXT(B5,"AM/PM")) and is formated
to retur data like "Friday 9:30 AM" But the only problem is that after
12:00pm it's returning military time, so my other formulas wont work,
cause I'm using a Vlookup that's looking up a Day and time in standard
time, i.e. "Friday 1:30 PM" rather than "Friday 13:30 PM" If anyone can
help me it would be greatly appreciated. Thanks.

Works like a charm. lol, looks like I was doing my formula the long
way... Thanks for helping!!!

Bernie Deitrick wrote:
=TEXT(ROUND(B6*48,0)/48,"dddd H:MM AM/PM")

HTH,
Bernie
MS Excel MVP


"GTVT06" wrote in message
oups.com...
I need help getting my formula to return the time in standard time (not
military time) In cell B5 I have =NOW() formula, formated to return
data like "August 18, 2006" and in cell B6 I have a formula to get the
time from B6 and round it up to the nearest 1/2hr. The formula I have
in B6 is =CONCATENATE(TEXT(B5,"dddddd"),"
",TEXT(ROUND(B5*48,0)/48,"H:MM")," ",TEXT(B5,"AM/PM")) and is formated
to retur data like "Friday 9:30 AM" But the only problem is that after
12:00pm it's returning military time, so my other formulas wont work,
cause I'm using a Vlookup that's looking up a Day and time in standard
time, i.e. "Friday 1:30 PM" rather than "Friday 13:30 PM" If anyone can
help me it would be greatly appreciated. Thanks.

Works like a charm. lol, looks like I was doing my formula the long
way... Thanks for helping!!!

Bernie Deitrick wrote:
=TEXT(ROUND(B6*48,0)/48,"dddd H:MM AM/PM")

HTH,
Bernie
MS Excel MVP


"GTVT06" wrote in message
oups.com...
I need help getting my formula to return the time in standard time (not
military time) In cell B5 I have =NOW() formula, formated to return
data like "August 18, 2006" and in cell B6 I have a formula to get the
time from B6 and round it up to the nearest 1/2hr. The formula I have
in B6 is =CONCATENATE(TEXT(B5,"dddddd"),"
",TEXT(ROUND(B5*48,0)/48,"H:MM")," ",TEXT(B5,"AM/PM")) and is formated
to retur data like "Friday 9:30 AM" But the only problem is that after
12:00pm it's returning military time, so my other formulas wont work,
cause I'm using a Vlookup that's looking up a Day and time in standard
time, i.e. "Friday 1:30 PM" rather than "Friday 13:30 PM" If anyone can
help me it would be greatly appreciated. Thanks.