LinkBack URL
About LinkBacks
Hello, I'm in the process of trying to subtract two military date time groups. For example say A1 is 060810DEC13 and A2 is 072300DEC13. How would I subtract these two items so that we can get the time in hours?
Thanks
Hello, I'm in the process of trying to subtract two military date time groups. For example say A1 is 060810DEC13 and A2 is 072300DEC13. How would I subtract these two items so that we can get the time in hours?
Thanks
What does 072300DEC13 represent as a date and time?
Entia non sunt multiplicanda sine necessitate
Hi and welcome to the forum
I think you have the day inthe 2nd date wrong (00 Dec?)
Assuming it was meant to be 10 Dec (doesnt really matter, just as long as the day is valid), try this...
This converts A to a proper date/time...
=DATEVALUE(RIGHT(A2,7))+TIMEVALUE(LEFT(A2,2)&":"&MID(A2,3,2))
And this does the same to A2...
DATEVALUE(RIGHT(A1,7))+TIMEVALUE(LEFT(A1,2)&":"&MID(A1,3,2))
Therefor...
=DATEVALUE(RIGHT(A2,7))+TIMEVALUE(LEFT(A2,2)&":"&MID(A2,3,2))-DATEVALUE(RIGHT(A1,7))+TIMEVALUE(LEFT(A1,2)&":"&MID(A1,3,2))
should give you what you want?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Maybe
060810DEC13 072310DEC13 A3: =(RIGHT(A2,7) & TEXT(LEFT(A2, 4), " 00\:00")) - (RIGHT(A1,7) & TEXT(LEFT(A1, 4), " 00\:00"))
Thank you both for your assistance, but my question is still left un-answered. And I have no idea how to better explain or get to the answer I need. From what I've seen, most are under the impression that a military date time group (DTG) is represented by DDMMMYY HH:MM. However, this is very much incorrect.
With the US Military DTG, the following is represented:
DDHHMMMMMYY
DD = Date
HHMM = Time (in 24 hours format)
MMM = Month
YY = Year
FD, your formula changed all dates to the first day of the month. The rest of the formula converted someone correctly.
Shg, what you suggest only shows a 1 hour, 15 minute difference. In reality, by looking at the day(s) in the example, it should be a total difference of 39 hours. Your formula does not allow for the advancement of time into the following day. 8:10 AM on Dec 6, to 11:10 PM on Dec 7 is a total of 39 hours.
Again, thank you both for your assistance, but it looks like I have a lot more research to do.
Break it up into 2 steps then...FD, your formula changed all dates to the first day of the month. The rest of the formula converted someone correctly.
060810DEC13 072310DEC13
B1=DATEVALUE(RIGHT(A1,7))+TIMEVALUE(LEFT(A1,2)&":"&MID(A1,3,2)) copied down
I agree, but the formula in B1 & B2 shows a date of 12/10/2013 when the date in A1 is (translated) 12/06/13 and B1 is 12/07/13. So your formula changes all the dates to the 10th of the month while a previous suggestion changed all the dates to the 1st of the month.
aahh ok I missed that the DD was 1st, so day/hour/month-month/year
change my formula to this, copied down...
=DATEVALUE(LEFT(A1,2)&RIGHT(A1,5))+TIMEVALUE(MID(A1,3,2)&":00")
then B2=B1 = 39
In Name manager, Define name: MONTHNAMES ----> ={"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"}
Then, with 060810DEC13 in A1:
Please Login or Register to view this content.
Data Range
Ben Van Johnson
And apparently I screwed something up. I'm getting [#VALUE!] in the cell that I'm putting the formula in. I've gone to "format cells" and changed the settings several times, but continue to get the same thing.
ProtonLeah,
When I use what you provided I keep getting #N/A. It must be something I'm doing on my end.
welcome to the forum, elothian. which formula did you use? if you're getting VALUE, there's no point formatting cells. you can try this formula too:
=--(LEFT(A2,2)&RIGHT(A2,5))+TEXT(MID(A2,3,4),"00\:00")-(--(LEFT(A1,2)&RIGHT(A1,5))+TEXT(MID(A1,3,4),"00\:00"))
upload a sample file to show us what you've got
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
Correction....
060810DEC13 072300DEC13
B1=DATEVALUE(LEFT(A1,2)&RIGHT(A1,5))+TIMEVALUE(MID(A1,3,2)&":"&MID(A1,5,2)) copied down
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks