Struggling to display the time properly in a formula

[john dalton]

Hi I have a formula and I looking to add the time onto the end of it.

The formula is

=TEXT(D49,"dddd ")&DAY(D49)&LOOKUP(DAY(D49),{1,2,3,4,21,22,23,24,31;"st","nd","rd","th","st","nd","rd","th","st"})&" of "&TEXT(D49,"mmmm yyyy")&" @ "&D47

D49 is the date and D47 is the time


the result at the moment is

Wednesday 1st of July 2015 @ 0.458333333333333

the result should be

Wednesday 1st of July 2015 @ 11.00am

Cheers in advance

John

[pepe74287]

Hi John,

Maybe something like this:

=TEXT(D49,"dddd ")&DAY(D49)&LOOKUP(DAY(D49),{1,2,3,4,21,22,23,24,31;"st","nd","rd","th","st","nd","rd","th","st"})&" of "&TEXT(D49,"mmmm yyyy")&" @ "&TEXT(D47,"hh:mm")

[john dalton]

Cheers

How do you get the am or pm part ?

John

[pepe74287]

Right, I forgot this part.. Change the last part of your formula to: TEXT(D47,"hh:mm AM/PM")

[john dalton]

Cheers pepe that works a treat mate.

John