Need help with a time formula, something custom for sure...

[dmanx1120]

So im currently using 0\:00 so I can just type 706 and it changes it to 7:06. How can I make it add 12 (military time basically) so that when I type 706 it will generate 19:06.

 

[dmanx1120]

or if I type something like 706+ it knows its military time or adds "12 or 12:00". Any help is appreciated! thanks.

 

[sshelt15]

Right click on the cell that you will be typing the time into and click 'format cells' > click 'time' on the left panel > click the military time option (it should show up as 13:30:00 or something similar). Hit ok. Clear the cell and type a time - it will work!

 

[Brian from Maui]

So im currently using 0\:00 so I can just type 706 and it changes it to 7:06. How can I make it add 12 (military time basically) so that when I type 706 it will generate 19:06.

Using this method, how would you tell the difference between AM or PM when entering 706?

 

[sshelt15]

I see what you are trying to do. A bit more complicated, but give this a shot. Let's say that you type all of the times in column A, starting in cell A1. Keeping with your example, let's say 706 is am and 706+ is pm.

In cell B1 - Type the following formula and hit CTRL + SHIFT + ENTER
=IF(RIGHT(A1,1)="+",SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW(1:25),1))*ROW(1:25),0),ROW(1:25))+1,1)*10^ROW(1:25)/10)+1200,SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW(1:25),1))*ROW(1:25),0),ROW(1:25))+1,1)*10^ROW(1:25)/10))

Right click cell B1 and put it in the custom time format that you are currently using, 0\:00. Drag the formula down column B. Let me know if this works.

 

[sshelt15]

Actually - I forgot to lock the cells so it would result in an error when you tried to drag it down column b! Use this formula instead:

=IF(RIGHT($A1,1)="+",SUMPRODUCT(MID(0&$A1,LARGE(INDEX(ISNUMBER(--MID($A1,ROW($1:$25),1))*ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)+1200,SUMPRODUCT(MID(0&$A1,LARGE(INDEX(ISNUMBER(--MID($A1,ROW($1:$25),1))*ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10))

 

[dmanx1120]

Using this method, how would you tell the difference between AM or PM when entering 706?

Could you some how use 706+ to let the computer know to generate military time? So if I typed 706 = 7:06 and 706+ = 19:06

 

[Brian from Maui]

Could you some how use 706+ to let the computer know to generate military time? So if I typed 706 = 7:06 and 706+ = 19:06

I suppose you could use something like,

=IF(RIGHT(A1,1)="x",TEXT(SUBSTITUTE(A1,"x",""),"00\:00")+0+1/2,TEXT(A1,"00\:00")+0)

but ideally, teach the users military time. You can still Custom Format the cell so entering colons wouldn't be necessary.