Convert Whole numbers to military time for calculations

N

NeonRedSharpie

Well-known Member

Joined

Jul 14, 2014

Messages

1,678

• Oct 8, 2014

• #2

Your whole number:

is 05:00 actually 500 or 5?

 

Upvote 0

R

Ron Abraham

New Member

Joined

Dec 3, 2011

Messages

15

• Oct 8, 2014

• #3

NeonRedSharpie said:

Your whole number:

is 05:00 actually 500 or 5?

Click to expand...

I enter 0500 for 5:00 AM and enter 1300 for 1:00 PM. I need to add the two for 18:00 - the rest of the spreadsheet then calculates mileage off the 18:00 (formatted as h:mm).

Thanks

 

Upvote 0

Scott Huish

MrExcel MVP

Joined

Mar 17, 2004

Messages

20,329

Office Version

1. 365

Platform

1. Windows

• Oct 8, 2014

• #4

=text(a1,"00\:00")+b1

 

Upvote 0

N

NeonRedSharpie

Well-known Member

Joined

Jul 14, 2014

Messages

1,678

• Oct 8, 2014

• #5

well, time is funny with excel. 1/24 = 1am, 2/24 = 2am, 3/24 = 3am.....etc. So, if you're entering in 500 for 0500, you can just use

Code:

=(A1/100/24) + B1

(assuming B1 is in time format already)

 

Upvote 0

R

Ron Abraham

New Member

Joined

Dec 3, 2011

Messages

15

• Oct 8, 2014

• #6

NeonRedSharpie said:

well, time is funny with excel. 1/24 = 1am, 2/24 = 2am, 3/24 = 3am.....etc. So, if you're entering in 500 for 0500, you can just use

Code:

=(A1/100/24) + B1

(assuming B1 is in time format already)

Click to expand...

Thanks - I'll try that at work tonight. Appreciate your reply


Ron

 

Upvote 0

shg

MrExcel MVP

Joined

May 7, 2008

Messages

21,836

Office Version

1. 2010

Platform

1. Windows

• Oct 8, 2014

• #7

NeonRedSharpie said:

So, if you're entering in 500 for 0500, you can just use

=(A1/100/24) + B1

(assuming B1 is in time format already)

Click to expand...

That doesn't work for other than whole hours, NRS.

Row\Col	A​	B​	C​
1​	1324​	13:14:24​	B1: =A1/100/24

Scott's does.

 

Upvote 0

N

NeonRedSharpie

Well-known Member

Joined

Jul 14, 2014

Messages

1,678

• Oct 8, 2014

• #8

shg said:

That doesn't work for other than whole hours, NRS.

Row\Col	A​	B​	C​
1​	1324​	13:14:24​	B1: =A1/100/24

<tbody>
</tbody>

Scott's does.

Click to expand...

Huh, I suppose you're right. That's what I get for testing with just a few pieces of sample data.

 

Upvote 0

shg

MrExcel MVP

Joined

May 7, 2008

Messages

21,836

Office Version

1. 2010

Platform

1. Windows

• Oct 8, 2014

• #9

Been there many times, most recently yesterday.

 

Last edited: Oct 8, 2014

Upvote 0

R

Ron Abraham

New Member

Joined

Dec 3, 2011

Messages

15

• Oct 9, 2014

• #10

This worked PERFECTLY. I have been harassed by this formula for 6 days. You are a Genius!

Thanks so much.

 

Upvote 0