Cells in time format. Trying to find a way to copy them the way i need.

[sakis_s]

Hi!
I'm trying to figure out how can i copy a cell that is in time format the way i need. I've tried different things but nothing works the way i want and i run out of ideas so i'm kindly asking if you have any other ideas to make it work.

My example:
In sheet1 A1 field is in "Time" format and i'm entering the value "13:00". What i see when looking the cell is "13:00" but if i click on it i see that actually the value is "1:00:00 pm".

Now i'm copying this field to sheet2 A2 field. I use formula "='sheet1'!a1". The result to the A2 field now of the new sheet is now "13". This is what i want.

Last step i want to join this field with the cell A3. A3 contains the word "Monday". If i enter "=A2&A3" to a new cell, the result will be "0.541666666666667Monday".
I tried to copy the A2 value with this formula to take it as text "=TEXT(A2;"00")" but it takes it as "01" and not as "13".

Anyone has an idea how to join this cell and keep the "13" value so the final result will be "13Monday"?

 

[Joe4]

Time copies to A2 cell from "sheet1" so actually the A2 cell contains "='sheet1'!a1" and brings value "13".
If i format the cell to General value "13" becomes "1".

This does seem to make any sense. I don't see how that could be. There must be something I am not understanding about your data structure.
Without looking at your file, I really cannot investigate what is going on there.

But if you already have an answer that seems to work for you, we can just leave well enough alone, and you can go with that.

 

[sakis_s]

This does seem to make any sense. I don't see how that could be. There must be something I am not understanding about your data structure.
Without looking at your file, I really cannot investigate what is going on there.

But if you already have an answer that seems to work for you, we can just leave well enough alone, and you can go with that.

Fortunately multiplying the cell with 24 actually works the way i want!

Thank you so much for your time Joe4 and i really appreciate your time to help me. All best!

 

[Joe4]

You are welcome.

And I am glad you were able to get a solution that works for you.