=NOW() Returning military time


G

GTVT06

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.
 
Ad

Advertisements

B

Bernie Deitrick

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
 
B

Bernie Deitrick

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
 
G

GTVT06

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

Advertisements

G

GTVT06

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

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top