Military time in excel

[priceassist]

I am working on a spreadsheet and my column E is my start time and column F is my stop time and i need to calculate the minutes in column G. =F-E=G doesn't always work. The time is military time and I want to be able to formulate it without needing to put the colons in. I want to be able to program the spreadsheet so when i plug in the start and atop time column G automatically calculates.

Start Stop Min
0827 1137
1158 1402
1438 1533
Total

0702 0814
0835 1137
1239 1534
1555 1737
1807 1914
1934 2118
Total

<colgroup><col span="4"></colgroup><tbody>
</tbody>

 

[Jonmo1]

Welcome to the board.

Try
=(TEXT(B2,"00\:00")-TEXT(A2,"00\:00"))*1440

A2 is Start Time, B2 is End Time.

 

[Obied70]

If you format columns E and F as 24hr time (ex used in excel is 13:30:55 I believe) then do your calculation in column G you will get hours:minutes:sec. You can then convert that to minutes.

Edit: So Column E is start, column F is end both formatted as 24hr time. Column G is F-E. In column J do =G*1440 to get total minutes. column J formatted as number.

 

[FDibbins]

If that is exactly what your data looks like...

Start Stop Min
0827 1137
1158 1402
1438 1533

Then that is probably text, not numeric/time, and you will need to convert it 1st (if it was true time, it would probably show 08:27 not just 0827, and if it was just a number, it probably would show just 827)

Check with =ISNUMBER(cell-ref)

 

[Obied70]

Couldn't edit anymore, my sick brain is making thinking a challenge. Column G format as number and enter the formula =(F-E)*1440

 

[FDibbins]

Obied, it all depends on what is really in the cells, and I have a feeling it is text. OP should test with =ISNUMBER)cell-ref)...FALSE indicates text

 

[gaz_chops]

Jonmo1's formula appears to work regardless if text or a value.

 

[Obied70]

Oh my bad he doesnt want to use colons. If using colon's wasn't too much, formatting as time and having his G column formatted as number its as simple as (F-E)*1440.

 

[SpillerBD]

Janmo1 nailed the simplicity.

I had seen the DOLLARDE and DOLLARFR functions before and can be useful. While more complicate than Janmo1's solution a little interesting is...

=((DOLLARDE(B2/100,60)/24)-(DOLLARDE(A2/100,60)/24))*(24*60)

 

[FDibbins]

Jonmo1's formula appears to work regardless if text or a value.

Yes it does, if it is text (which I suspect it is), but not if it is real time

	A​	B​	C​
2​	0827	1137	190​
3​	8:27​	11:37​	0​

1st row is text *time*, 2nd row is real time

(unless I am missing something, which is entirely possible lol)