How do I create text string using hh:mm without is changing to decimal fraction?

Hi all,

I have a timesheet that I fill every day, sometimes I split my hour-long work and have 2 or even 3 sessions, all of which need reporting in PST (I'm GMT).

The attached image shows my set-up, I put in my start/stop time and it gets switched to PST in the 4th and 5th column.

It's a pain to copy and paste from two excel boxes as the formatting still needs changing but this would be so much quicker and easier if I could create a string box containing my start and stop times with a dash in between them.

I found the fourmula =D22 & " - " & C22 but it changes the times (ie 15:22) to a decimal fraction.

I have my time columns and the one I'm using for the test formula formatted as Custom/time/hh:mm and have tried all the other formats but none allow this, all of them change the time to a fraction except text, which just puts the cell number!

Any help would be great, thanks!

sheet clip.png

Try it this way in K28:

=TEXT(D28,"[h]:mm") & " - " & TEXT(E28,"[h]:mm")

or you might prefer:

=TEXT(D28,"hh:mm") & " - " & TEXT(E28,"hh:mm")

though this will wrap every 24 hours.

Hope this helps.

Pete

Brilliant thanks all - I have a solution that works, I can use this on another sheet too and will save me much time and fiddlyness!

Thank you all for replying!

Glad to hear that you found a solution, and thanks for marking the thread as Solved.

You might also like to know that you can show your appreciation directly and thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete