=NOW() Returning military time

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" <[email protected]> wrote in message
news:[email protected]...
>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
news:e%[email protected]...
> =TEXT(ROUND(B6*48,0)/48,"dddd H:MM AM/PM")
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "GTVT06" <[email protected]> wrote in message
> news:[email protected]...
>>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
news:e%[email protected]...
> =TEXT(ROUND(B6*48,0)/48> HTH,
> Bernie
> MS Excel MVP
>
>
> "GTVT06" <[email protected]> wrote in message
> news:[email protected]...
>>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" <[email protected]> wrote in message
> news:[email protected]...
> >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" <[email protected]> wrote in message
> news:[email protected]...
> >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.
> >