Reference a TIME formatted cell from a TEXT formatted cell

[wyndland]

I have prepared a teacher timetable as a spreadsheet and now want to present the nitty gritty in a plain English way.

My hours worked cells are all formatted to custom [h]:mm but the plain English text cells are formatted to text(!)

Problem: When I reference the cell ="Teacher name works "&Their hours here&" per week" (where the 'Their hours here' refers to cell H5), I can get a decimal representation with no problem, but REAL time is eluding me.

Any help please?

Cheers

Glen

 

[oldchippy]

Hi wyndland,


Try this


="Teacher name works "&TEXT(H5,"[h]:mm")&" per week"

 

[wyndland]

Old Chippy you are a star!

Having gone 'round the houses this little conundrum was driving me potty

Absolutely stunning.

I thank you

 

[oldchippy]

Thanks for the feedback, glad it worked for you