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]

deetel

 

[awoohaw]

I see the month in 1st column? where is the YEAR?
Is the day always the 3&4th characters of 5 in the 4th column?

 

[awoohaw]

try:

Book1
	A	B	C	D	E	F	G	H	I
1	Month			Day		start hr	end hr
2	MAY			**15*		800	1600	ddhhmm mth yy	151600 May 23
Sheet1

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

 

[dfull]

deetel

The *'s are another form of numbering used for a different purpose. sometimes they are letters sometimes they are numbers, I blanked them out to avoid confusion and to only focus on the part that data can be pulled from. The date is only in written form in A, year is assumed 2023, would change the actual formula to reflect 2024 when the time comes. Thanks!

 

[awoohaw]

ERRORS ABOVE. This is better, not sure it is what you want:

Book1
	A	B	C	D	E	F	G	H	I
1	Month			Day		start hr	end hr
2	MAY			**15*		800	1600	ddhhmm mth yy	151600 May 23
3	JUN			**15*		1800	1600	ddhhmm mth yy	161600 Jun 23
Sheet1

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

note this does not change your minutes calculation, if you have miniutes in columnG the result will not be accurate.

 

[awoohaw]

this will include minutes. additionally, it will be accurate if going to the next day or same day:

=DATE(2023,SWITCH(A3,"JAN",1,"FEB",2,"MAR",3,"APR",4,"MAY",5,"JUN",6,"JUL",7,"AUG",8,"SEP",9,"OCT",10,"NOV",11,"DEC",12),MID(D3,3,2)) +IF(F3>G3,1,0) + TIME((G3/100),MOD(G3,100),0)

the cell format is: ddhhmm mmm yy

 

[dfull]

Overall i'm trying to add a column that automatically converts these inputs to that format on this tracker we use, since right now we need to write out DTG by hand for every single line (sometimes hundreds of lines) for a system that only recognizes DTG format.

I've copied and pasted this code, however its giving out "#Name?". I'm sure I've just made a simple mistake though i can't seem to spot it. Sorry for the headache and thanks so much for the help so far! (i've only changed the source row to match the row i'm utilizing)

 

[awoohaw]

okay, SWITCH Must not be in 2016.

TRY THIS:

IFS may not be in 2016 either.

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, IFS(A2="JAN",1,A2="FEB",3,A2="MAR",3,A2="APR",4,A2="MAY",5,A2="JUN",6,A2="JUL",7,A2="AUG",8,A2="SEP",9,A2="OCT",10,A2="NOV",11,A2="DEC",12),MID(D2,3,2)) +IF(F2>G2,1,0) + TIME((G2/100),MOD(G2,100),0)
J2:J3	J2	=TEXT(DATE(2023, IFS(A2="JAN",1,A2="FEB",3,A2="MAR",3,A2="APR",4,A2="MAY",5,A2="JUN",6,A2="JUL",7,A2="AUG",8,A2="SEP",9,A2="OCT",10,A2="NOV",11,A2="DEC",12),MID(D2,3,2)) +IF(F2>G2,1,0) + TIME((G2/100),MOD(G2,100),0),"ddhhmm mmm yy")

 

[awoohaw]

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