Time Conversion

D5GRVTY · Post by **D5GRVTY** » Tue Sep 27, 2005 12:17 pm

Does anyone know of any software that will take the time diffrence in HH:MM format and covert it into a decimal format.

Example:

UP: 20:00
DWN: 20:36

Airtime: 0.6

Thanks

D5

ahramin · Post by **ahramin** » Tue Sep 27, 2005 12:58 pm

Microsoft Excel

___A
1 20:00
2 20:36
3 =(A2-A1)*24

D5GRVTY · Post by **D5GRVTY** » Tue Sep 27, 2005 1:18 pm

The problem is the rounding up or down, I need something pretty foolproof (Not to say that complete fools are making entries in our journey logs but........)

ahramin · Post by **ahramin** » Tue Sep 27, 2005 1:31 pm

=ROUND((A3),1)

Right Seat Captain · Post by **Right Seat Captain** » Tue Sep 27, 2005 1:55 pm

In MS Excel, you can go to the cell properties and choose how many decimals are displayed. Take the difference of the two cells (A2-A1) then divide by 60. This gives you a decimal. Set the cell to display the value to 1 decimal, and voila, its rounded.

D5GRVTY · Post by **D5GRVTY** » Tue Sep 27, 2005 2:35 pm

Thanks a ton ahramin, I owe you a beer,PM if you're in ever in the YYC area

Now I just need your help with one last thing

It's almost there but excell does'nt round the way that I need it to.

I'm trying to acomplish this

0-02 = 0.0
03-08= 0.1
09-14= 0.2
15-20= 0.3
21-26= 0.4
27-32= 0.5
Etc.

I tried using ROUNDUP but I can't get excell to round the way transport has rounded.

Thanks again

D5

Jetman28 · Post by **Jetman28** » Tue Sep 27, 2005 4:19 pm

Ok, Excel works if the flights begin and end on the same day, but what about departing at 23:30 and landing at 01:30 the next day? The answer Excel comes up with is -22.

ahramin · Post by **ahramin** » Tue Sep 27, 2005 5:58 pm

Uh, D5GRVTY, maybe you missed my second post:

=ROUND((A3),1)

Will round the previous formula [(time2-time1)*24] to one decimal place, just the way you want it. You can do this in one cell:

=ROUND(((A2-A1)*24),1)
Where A1 is your up time and A2 is your down time.

As for YYC, i will be there tomorrow.

Jetman28, if you put the date in front of the hours and minutes, you will not have overrun errors.

D5GRVTY · Post by **D5GRVTY** » Mon Oct 03, 2005 4:02 pm

Thanks everybody for the help, now if you could help me solve one last mystery.

So using ahramin's formula's works if the time is over an hour

For example a student completes his/her first solo with an air time of 9min, using =(A2-A1)*24 gets us 0.15 and =ROUND((A3),1) rounds it to 0.1.

Now another student takes and airplane and has an airtime of 1h09min, using =(A2-A1)*24 gets us 1.15 and =(A2-A1)*24 rounds it to 1.2.

So why is 0.15 rounded to .1 and 1.15 rounded to 1.2?

I apologize in advance if my basic understanding of math and rounding is wrong.

Thanks

D5

Cargodog · Post by **Cargodog** » Mon Oct 03, 2005 5:28 pm

You do need to be careful with that formula a bit as if the time crosses midnight you'll have some issues.

Works fine with the example 20:20-20:15=5 min, but 20:20-00:10 will go sideways on ya.

I think it has to do with the computer as seeing time as linear and when it crosses midnight.

ahramin · Post by **ahramin** » Mon Oct 03, 2005 9:15 pm

cargodog, you will not have this problem if you put the date in as well. As in

Takeoff 1/1/2005 20:20
Landing 1/2/2005 00:10

Now as for our lovely excel rounding situation.

My spreadsheet does the same thing. 9 minutes gives .1 but 69 minutes gives 1.2 for me as well. I now have a spreadsheet 12 wide and 1440 long. If you make it count in threes it works fine but counting in ones the odd .05s get rounded down and the even .05s get rounded up.

Actually the problem is rather simple. Somewhere deep in the bowels of the code that 0.15 is being turned into 0.1499999999999999999999999999999 and then some more nines. The missing 1 is so far down the line that it is beyond the number of significant figures printed into a cell, but not beyond the number of significant figures calculated by the program. So it is a bug in the way Excel deals with minutes.

However there is an easy fix. Change =ROUND((A3),1) to =ROUND(ROUND(A3,2),1)

This will round the .14999999999999 to an exact 0.15 first which will then be rounded properly to .2

I will see if there is a better way of dealing with time so that we don't get this lost sig fig bug but don't expect an answer anytime soon.

Blue Yonder · Post by **Blue Yonder** » Mon Oct 03, 2005 9:36 pm

Another way with excel to go over 24:00 (midnight) with 25:00 (1AM), 26:00 (2AM) and so on and so on...

Blue

ahramin · Post by **ahramin** » Tue Oct 04, 2005 7:27 am

Oooo. Never thought of that one. That would be faster than putting in the date.

Cargodog · Post by **Cargodog** » Tue Oct 04, 2005 11:03 am

Blue Yonder,

True, but the problem with that is if you're using the formula in a schedule or something and don't want 25:00 or 28:00 etc all over the place...

It's quck and dirty and will do the job, but going to confuse anyone that looks at what you've got listed - or think you're a nutbar...

Ahramin, ah, haven't tried that before (with the date), but works nicely. But, the one thing I don't like about it is if I had a long list of times. Let's say a schedule and wanted to listed Dep & Arr times and then calculate a block time in one of the columns. The dates beside every time gets a bit messy to look at (may get cluttered). So what about throwing in an "if" statement, whereby if the time is a negative number then add 1440 minutes.

For example 23:30 to 00:10. Without dates the time would be negative -23:20 or 1400 minutes, or .9722222222 of a day. If you simple did the IF statement you could state that since the number is negative, add 1440 minutes to the result. The answer in this case would be -1400 minutes + 1440 minutes = +40 minutes, which is correct.

Eg...

Dep 23:30
Arr 0:10
Diff 40
Formula =IF((A2-A1)<0,((A2-A1)*1440)+1440,(A2-A1)) where A2 is the arrival time, A1 is the departure time.

Blue Yonder · Post by **Blue Yonder** » Tue Oct 04, 2005 1:35 pm

If the cells are formatted as "Custom" and set the type to 'h:mm' the display will be 1:00 if you enter 25:00. The rest should work as advertised.

Blue.

Cargodog · Post by **Cargodog** » Tue Oct 04, 2005 5:19 pm

Interesting, yep, it does work...

I guess I just not a fan of entering times like 25:00, 26:00 etc and just prefer a formula that'll do the work and allow the coversion...

Thanks though, didnt realize that custom format would do the job...

ahramin · Post by **ahramin** » Tue Oct 04, 2005 10:30 pm

Cargodog, yes if you have the wherewhithal to understand and write IF statements then it is not a bad solution. Certainly as good as using 25 hours for 1 AM. In fact when i was writing my logbook program i got lazy and used huge nested IF functions instead of properly figuring out how to work with dates in Excel.

The only problem with this is it is totally unportable and very open to bugs. But in most cases it works fine.