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]

Try:

TEXT(A2;"[h]") & A3

 

[Dave Patton]

Hopefully the following provides some help

Date and Time 2021.xlsm
	A	B	C	D	E
1	13:00	13	13Monday
2
3			Tue Jan-26-21 10:37 AM
4
3a

Cell Formulas
Range	Formula
B1	B1	=A1*24
C1	C1	=B1&"Monday"
C3	C3	=TEXT(NOW(),"ddd mmm-dd-yy HH:MM AM/PM")

 

[sakis_s]

Hopefully the following provides some help

Date and Time 2021.xlsm
	A	B	C	D	E
1	13:00	13	13Monday
2
3			Tue Jan-26-21 10:37 AM
4
3a

Cell Formulas
Range	Formula
B1	B1	=A1*24
C1	C1	=B1&"Monday"
C3	C3	=TEXT(NOW(),"ddd mmm-dd-yy HH:MM AM/PM")

Oh my! Never thought to multiply with 24. That was so simple! Thank you so much sir, i really appreciate your help! All best!

 

[Joe4]

Oh my! Never thought to multiply with 24. That was so simple! Thank you so much sir, i really appreciate your help! All best!

Did you try my suggestion?
There shouldn't be any need to to do that. I was able to get what you want with one simple formula.

 

[sakis_s]

Did you try my suggestion?
There shouldn't be any need to to do that. I was able to get what you want with one simple formula.

Of course i tried but i don't get a result. The cell turns "#VALUE!"

 

[Joe4]

Of course i tried but i don't get a result. The cell turns "#VALUE!"

Are you in US or Europe? I assumed Europe because in your original post, you were using ";" as the argument delimiter in your formula, but if in US where the comma is the argument delimiter, then try:

=TEXT(A2,"[h]") & A3

 

[sakis_s]

Are you in US or Europe? I assumed Europe because in your original post, you were using ";" as the argument delimiter in your formula, but if in US where the comma is the argument delimiter, then try:

=TEXT(A2,"[h]") & A3

No i'm in Europe. There is no problem with ";" this is what i use. The problem must be with "[h]". When i write this it doesn't change color to understand this as argument. It's like this parameter is not valid for Excel.

 

[Joe4]

No i'm in Europe. There is no problem with ";" this is what i use. The problem must be with "[h]". When i write this it doesn't change color to understand this as argument. It's like this parameter is not valid for Excel.

That is really odd. Your time value is in cell A2, right?
If you temporarily change the format of that cell to General, what does it show?

 

[sakis_s]

That is really odd. Your time value is in cell A2, right?
If you temporarily change the format of that cell to General, what does it show?

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