autocalculating military Date time group

**john.michaels** · 12-23-2014, 12:27 AM

Hello all,
So glad to have discovered this forum. I am really hoping you all can help me with a couple issues i am having. I am currently in the miltary in North Carolina trying to make a spreadsheet for parts tracking information. One thing i really need help on is how to create a formula to auto calculate military date time groups. Format as follows DD(double digit Day)HH(double digit hour)MM(Double digit minute)Z(time format)MON(current month three letter)YR(two digit year identifier) so example: how many hours are there between 230400ZDEC14 and 210300ZDEC14? i can do it manually. However, it would really help me out if any of you had a solution. Thank you in advance.

**ashishmehra2010** · 12-23-2014, 12:40 AM

Hi John,

Consider values in cells A1 & B1; the formula would be

Formula:

=MID(A1,3,2)-MID(B1,3,2)

Regards,
AM

**Crooza** · 12-23-2014, 12:57 AM

Ashishmehra2010 that doesn't seem to take into account these are different days hence another 48 hours (23 - 21) x 24

**ashishmehra2010** · 12-23-2014, 01:16 AM

Hi Crooza,

Sorry its my mistake.

Check this

Formula:

=(LEFT(A1,2)-LEFT(B1,2))*24+(MID(A1,3,2)-MID(B1,3,2))

Regards,
AM

**Crooza** · 12-23-2014, 01:16 AM

Try this to convert your code to a date /time, and this will take into account the day, year and month too

=DATE((2000+VALUE(RIGHT(A1,2))),VLOOKUP(MID(A1,8,3),{"Jan",1;"Feb",2;"Mar",3;"Apr",4;"May",5;"Jun",6;"Jul",7;"Aug",8;"Sep",9;"Oct",10;"Nov",11;"Dec",12},2,0),LEFT(A1,2))+MID(A1,3,2)/24

If you use this to calculate Cells a1 and B1 you can subtract one from the other and multiply by 24 to calculate the hours

if you want to do this in one entire formula then this monster will work

=((DATE((2000+VALUE(RIGHT(A1,2))),VLOOKUP(MID(A1,8,3),{"Jan",1;"Feb",2;"Mar",3;"Apr",4;"May",5;"Jun",6;"Jul",7;"Aug",8;"Sep",9;"Oct",10;"Nov",11;"Dec",12},2,0),LEFT(A1,2))+MID(A1,3,2)/24)-(DATE((2000+VALUE(RIGHT(A2,2))),VLOOKUP(MID(A2,8,3),{"Jan",1;"Feb",2;"Mar",3;"Apr",4;"May",5;"Jun",6;"Jul",7;"Aug",8;"Sep",9;"Oct",10;"Nov",11;"Dec",12},2,0),LEFT(A2,2))+MID(A2,3,2)/24))*24

autocalculating military Date time group

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

autocalculating military Date time group

Re: autocalculating military Date time group

Re: autocalculating military Date time group

Re: autocalculating military Date time group

Re: autocalculating military Date time group

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Converting Text to Military Date Time Group Format

Military date time group subtraction

[SOLVED] Covert date/time to military time hour only

Simple way to convert military time to standard where military has no colon

Excel 2007 : DateTime Difference with Military Date/time

Bookmarks

Bookmarks

Posting Permissions