Convert Time into Military DTG from Multiple cells

[dfull]

Good morning!

I'm looking to convert normal time inputs into a single cell in a DTG format, i have an example of how the data is laid out on the spreadsheet below

In I3 is the time format i neet, pulling from A3, D3 (the date is embedded between other numbers I changed to stars. G3 is the start time and not needed, only there for reference. H3 is the ending time that needs to go into the conversion. The part that I got stuck on is that sometimes the end time is on the next date and I can't think of a solution where the formula could account for that, as shown in row 4, where the start time is at 1630 on the 16th, however the end time is at 0730 the 17th. Anybody know of a possible solution?

Thanks a million!

 

[awoohaw]

Hi Again,
I was just reminded of a "trick" with the MONTH function that makes your formula much easier. Try this:

Mr excel questions 35.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Month			Day		start hr	end hr		value	formatted value as text
2	MAY			**15*		800	1630	ddhhmm mth yy	151630 May 23	151630 May 23
3	JUN			**15*		1800	1630	ddhhmm mth yy	161630 Jun 23	161630 Jun 23
dfull

Cell Formulas
Range	Formula
I2:I3	I2	=DATE(2023, MONTH(A2&"1"), MID(D2,3,2)) + IF(F2>G2,1,0) + TIME((G2/100),MOD(G2,100),0)
J2:J3	J2	=TEXT(DATE(2023, MONTH(A2&"1"), MID(D2,3,2)) + IF(F2>G2,1,0) + TIME((G2/100),MOD(G2,100),0),"ddhhmm mmm yy")

 

[dfull]

I'm more certain about this one:

Mr excel questions 35.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Month			Day		start hr	end hr		value	formatted value as text
2	MAY			**15*		800	1630	ddhhmm mth yy	151630 May 23	151630 May 23
3	JUN			**15*		1800	1630	ddhhmm mth yy	161630 Jun 23	161630 Jun 23
dfull

Cell Formulas
Range	Formula
I2:I3	I2	=DATE(2023, IF(A2="JAN",1, IF(A2="FEB",3, IF(A2="MAR",3, IF(A2="APR",4, IF(A2="MAY",5, IF(A2="JUN",6, IF(A2="JUL",7, IF(A2="AUG",8, IF(A2="SEP",9, IF(A2="OCT",10, IF(A2="NOV",11,12))))))))))), MID(D2,3,2)) + IF(F2>G2,1,0) + TIME((G2/100),MOD(G2,100),0)
J2:J3	J2	=TEXT(DATE(2023, IF(A2="JAN",1, IF(A2="FEB",3, IF(A2="MAR",3, IF(A2="APR",4, IF(A2="MAY",5, IF(A2="JUN",6, IF(A2="JUL",7, IF(A2="AUG",8, IF(A2="SEP",9, IF(A2="OCT",10, IF(A2="NOV",11,12))))))))))), MID(D2,3,2)) + IF(F2>G2,1,0) + TIME((G2/100),MOD(G2,100),0),"ddhhmm mmm yy")

That works! Thanks so much for you help and this doozy of a formula!

 

[awoohaw]

hi, you're welcome, I"m happy you found a solution here.
Best Wishes!!!