Formula to format Time Punch

[nhawkins]

Hi,

I am trying to re-format a time punch into a new date column using a formula.

Currently there is a date column in the following format:
[TABLE="width: 61"]
<tbody>[TR]
[TD]01/01/17[/TD]
[/TR]
</tbody>[/TABLE]

There is another column in the sheet with the clock in times which does not have the date in the proper format (example of dates shown below):
[TABLE="width: 47"]
<colgroup><col></colgroup><tbody>[TR]
[TD]158[/TD]
[/TR]
[TR]
[TD]0917[/TD]
[/TR]
[TR]
[TD]2042
[/TD]
[/TR]
</tbody>[/TABLE]

I would like to create a new column which combines both into the excel date formatting so I can input it into a different template.

Thanks so much for your help!

 

[PaddyD]

Hi - welcome to the board.

Could you indicate what dates those values are meant to represent?

 

[nhawkins]

Hi - welcome to the board.

Could you indicate what dates those values are meant to represent?

Thank you!
These are the times people clock in
158 = 1:58 AM
0917= 09:17 AM
2042 = 8:42 PM

The dates are housed in another column and id like to combine them into one column but am having difficulty with there being no : to separate hours and minutes :?

Thanks again!

 

[jtakw]

Hi,

You can do one of these,

Formula in D1 will convert your A1 and B1 data to show the way you want, BUT it's result is TEXT.
Formula in F1 will convert your A1 and B1 data and give you a NUMBERIC result so you can do further calculations with in, but you'll have to custom FORMAT the cell to: mm/dd/yy hh:mm AM/PM

Book1
	A	B	C	D	E	F	G	H	I	J
1	01/01/17	158		01/01/17 01:58 AM		01/01/17 01:58 AM		FALSE		TRUE
2	01/02/17	0917		01/02/17 09:17 AM		01/02/17 09:17 AM
3	01/03/17	2042		01/03/17 08:42 PM		01/03/17 08:42 PM
Sheet19

Cell Formulas
Range	Formula
H1	=ISNUMBER(D1)
J1	=ISNUMBER(F1)
D1	=TEXT(A1,"mm/dd/yy")&" "&TEXT(LEFT(B1,LEN(B1)-2)&":"&RIGHT(B1,2),"hh:mm AM/PM")
F1	=A1+TEXT(LEFT(B1,LEN(B1)-2)&":"&RIGHT(B1,2),"hh:mm AM/PM")

 

[nhawkins]

Hi,

You can do one of these,

Formula in D1 will convert your A1 and B1 data to show the way you want, BUT it's result is TEXT.
Formula in F1 will convert your A1 and B1 data and give you a NUMBERIC result so you can do further calculations with in, but you'll have to custom FORMAT the cell to: mm/dd/yy hh:mm AM/PM

	A	B	C	D	E	F	G	H	I	J
01/01/17 01:58 AM
01/02/17 09:17 AM
01/03/17 08:42 PM

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]01/01/17[/TD]
[TD="align: right"]158[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]01/01/17 01:58 AM[/TD]
[TD="align: right"][/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"][/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]01/02/17[/TD]
[TD="align: right"]0917[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]01/02/17 09:17 AM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]01/03/17[/TD]
[TD="align: right"]2042[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]01/03/17 08:42 PM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet19​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H1[/TH]
[TD="align: left"]=ISNUMBER(D1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J1[/TH]
[TD="align: left"]=ISNUMBER(F1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D1[/TH]
[TD="align: left"]=TEXT(A1,"mm/dd/yy")&" "&TEXT(LEFT(B1,LEN(B1)-2)&":"&RIGHT(B1,2),"hh:mm AM/PM")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F1[/TH]
[TD="align: left"]=A1+TEXT(LEFT(B1,LEN(B1)-2)&":"&RIGHT(B1,2),"hh:mm AM/PM")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

This worked great! Thank you so so much!!

 

[jtakw]

You're welcome, welcome to the forum.