Convert Time into Military DTG from Multiple cells

dfull

New Member
Joined
May 16, 2023
Messages
4
Office Version
  1. 2016
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
1684227052002.png


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!
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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?
 
Upvote 0
try:
Book1
ABCDEFGHI
1MonthDaystart hrend hr
2MAY**15*8001600ddhhmm mth yy151600 May 23
Sheet1
Cell Formulas
RangeFormula
I2I2=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)
 
Upvote 0
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!
 
Upvote 0
ERRORS ABOVE. This is better, not sure it is what you want:
Book1
ABCDEFGHI
1MonthDaystart hrend hr
2MAY**15*8001600ddhhmm mth yy151600 May 23
3JUN**15*18001600ddhhmm mth yy161600 Jun 23
Sheet1
Cell Formulas
RangeFormula
I2:I3I2=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.
 
Upvote 0
this will include minutes. additionally, it will be accurate if going to the next day or same day:

Excel Formula:
=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
 
Upvote 0
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)
1684235963276.png
 
Upvote 0
okay, SWITCH Must not be in 2016.

TRY THIS:

IFS may not be in 2016 either.

Mr excel questions 35.xlsm
ABCDEFGHIJ
1MonthDaystart hrend hrvalueformatted value as text
2MAY**15*8001630ddhhmm mth yy151630 May 23151630 May 23
3JUN**15*18001630ddhhmm mth yy161630 Jun 23161630 Jun 23
dfull
Cell Formulas
RangeFormula
I2:I3I2=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:J3J2=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")
 
Upvote 0
I'm more certain about this one:

Mr excel questions 35.xlsm
ABCDEFGHIJ
1MonthDaystart hrend hrvalueformatted value as text
2MAY**15*8001630ddhhmm mth yy151630 May 23151630 May 23
3JUN**15*18001630ddhhmm mth yy161630 Jun 23161630 Jun 23
dfull
Cell Formulas
RangeFormula
I2:I3I2=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:J3J2=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")
 
Upvote 1
Solution

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top