Excel Formula

[Noted]

Trying to add several numbers in the format ( STD . MIN ) The only formula I have come up with is for adding two numbers in that format. Formula:=(INT(SUM(A1:A2))+INT(100*((A1-INT(A1))+(A2-INT(A2)))/60)+(MOD(100*(A1-INT(A14)+(A2-INT(A2)));60)/100))
any ideas.

:eek: :eek:

[ 11 August 2001: Message edited by: Noted ]

[Icarus]

Certainly looks messy and there probably is a better way, however, perhaps you could expand on exactly what you are trying to achieve; think I may be able to help more then. (STD.MIN ?? :confused: ).
The formula certainly works in theory, i.e.
It returns the integer value of the sum of three other values; but without a real-world scenario to make a comparison to it is difficult to see a better way.
Also did you mean (a1-INT(a14)) or should that be (a1-INT(a1))?

[ 12 August 2001: Message edited by: Icarus ]

[Noted]

Icarus. Thanks for the reply. You are correct, it is supposed to be (a1-INT(a1).
I have the predicted flight hours in the format ( hh . mm ) and not in the standard format of ( hh : mm ), that's what makes it a bit difficult and that's the reason for the formula. It is about 20 flight legs that I'm trying to add. At the moment the formula looks something like this:
=(INT(SUM(D4 :D23))+INT(100*((D4-INT(D4))+(D6-INT(D6))+(D7-INT(D7))+(D8-INT(D8))+(D9-INT(D9))+(D10-INT(D10))+(D11-INT(D11 ))+(D12-INT(D12))+(D13-INT(D13)))/60)+(MOD(100*(D4-INT(D4)+(D6-INT(D6)+(D7-INT(D7)+(D8-INT(D8)+(D9-INT(D9)+(D10-INT(D10) +(D11-INT(D11)+(D12-INT(D12)+(D13-INT(D13))))))))));60)/100))

I am sure there is an easier way, just have not found it yet.

:confused: :confused:

[ 12 August 2001: Message edited by: Noted ]

[ 12 August 2001: Message edited by: Noted ]

[Icarus]

Seems you maybe making life difficult for yourself. Is there a reason for not wanting to use two columns, one (Column-A) for HH and the other (Column-B) for MM.

Then you could:

SUM(Ax:Ay) + INT(SUM(Bx:By)/60) for HH
and
MOD(SUM(Bx:By),60) for MM.

[Noted]

Well, its not me trying to make it difficult on myself, it's the system. The one computer program ( system ) I'm marking and copying the flight hrs. from, is set up in the format ( hh . mm )that's the reason for all this. I got it to work so it will add about 19 cells, except I get an error every 5th cell for some reason.

:confused:

[Icarus]

OK give me another day and I'll see what i can come up with! :D
One question before I retire for the night, are you cutting/pasting direct into cells/a column or manually entering the data?
If pasting, would 4H33M be 4.33 or 04.33?

[ 12 August 2001: Message edited by: Icarus ]

[Background Noise]

Don't you need SUM(INT.... rather than INT(SUM.... ? I think it will make a difference with more than 2 times to add up.

You need to sum the INTs then sum the decimalised fractions (value - INT(value)). Then add the INT of the summed fractions to the INT total, then undecimalise the fraction and add it to the INT total - I think.

I may know someone who's done hrs and minutes in excel?

[Gash Handlin]

I don't really know what you're trying to achieve with the formula but for years I've been trying to get a formula to add up my hours so I can have a home made electronic logbook.

Last week I finally discovered the easy way to add times together and display a total that is greater than 24hours.

The problem is that when you have a column of times and add it to another column of times the answer will always try to show you a time i.e. upto 2400.

the simple solution is to change the format of the cells in the result column from hh:mm to [h]:mm hey presto no limit on the number of hours.

couldn't believe how simple it was yet it isn;t mentioned in any of the online help I searched.

[ 13 August 2001: Message edited by: Gash Handlin ]

[Icarus]

Gash - you are absolutely correct, however, that requires the HHMM data to be entered as HH:MM rather than HH.MM which is what is going on (i.e. the problem to be overcome)here I believe.

What we need to do here is to take a Base60 number and convert it to a Base10 number; then do the addition(s) to provide a Base10 total; then convert that total to a Base60 number.

Still working on it; will get there today i hope! ;)

[ 13 August 2001: Message edited by: Icarus ]

[Icarus]

Well, thinking about Base10 and Base60 logarithms at the moment! Not getting too far!

The quick fix would be the following MACRO:

Sub ReplaceDecimal()
'
' ReplaceDecimal Macro

'
Application.CommandBars("Stop Recording").Visible = False
Cells.Replace What:=".", Replacement:=":", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

And run this after your data entry.
This will replace . with : and Excel will understand the time values.
Then as advised above make sure you format the SUM cell as num,ber format [H]:mm

Will kep working and let you know how I get on.

[Noted]

This is it, well almost.

=(INT(SUM(D4 :D23))+INT(100*((D4-INT(D4))+(D6-INT(D6))+(D7-INT(D7))+(D8-INT(D8))+(D9-INT(D9))+(D10-INT(D10))+(D11-INT(D11 ))+(D12-INT(D12))+(D13-INT(D13))+(D14-INT(D14))+(D15-INT(D15))+(D16-INT(D16))+(D17-INT(D17))+(D18-INT(D18))+(D19-INT(D19 ))+(D20-INT(D20))+(D21-INT(D21))+(D22-INT(D22))+(D23-INT(D23)))/60)+(MOD(100*(D4-INT(D4)+(D6-INT(D6)+(D7-INT(D7)+(D8-INT (D8)+(D9-INT(D9)+(D10-INT(D10)+(D11-INT(D11)+(D12-INT(D12)+(D13-INT(D13)+(D14-INT(D14))+(D15-INT(D15))+(D16-INT(D16))+(D 17-INT(D17))+(D18-INT(D18))+(D19-INT(D19))+(D20-INT(D20))+(D21-INT(D21))+(D22-INT(D22))+(D23-INT(D23)))))))))));60)/100) )

That formula adds 20 flight legs in the format. ( hh . mm ), which would suit me fine. I am sure there is an easier way, still looking for it. There is only one problem with it, which I have not worked out yet. You can try for yourself and see what happens at ( D10 ), ( D15 ) and ( D20 ) using ( 1.20 ) for every step.

:confused: :confused: :confused:

[PTT]

Do you have to put the smiley in?

[Icarus]

I guess the smiley is appearing because of the -colon d- to indicate the range D4 to D23, and the bulletin board software is interpreting it as :D

[Noted]

The smiley is not part of the formula, but I will put some in, once I get it to work. Icarus, any solution yet, thanks for the help sofar.

:D :D

[Agent86]

Another way around all this is to change the time separator in the Regional settings to a period "." It is not there as a selectable option but just type it in and Apply.
This however WILL cause you much grief if you leave it that way :eek: :eek: so I would suggest changing it, running your calculations and then CHANGE IT BACK. There are other programmable ways around it by using the instr function to search for the "." and then the left and mid functions to split up the hours and minutes This would only be necessary if the input does not have leading zeroes ie 5.45 Vs 05.45, other wise left(value,2) = the hour and right(value,2) = the minute.

If the file is small enough email me a sample and I will have a look.

[Mishandled]

Heres my punt. copy this formula into the cells to the right of your source data, fill it down and then sum it up. I think it works.

=VALUE(LEFT(H4;SEARCH(".";H4)-1))+(VALUE(RIGHT(H4;2)))/60

(BTW im using a Swiss version of Excel. I think in an english version the semi-colons should be commas.)

[ 14 August 2001: Message edited by: Mishandled ]

[Onan the Clumsy]

I just skimmed the messages, so sorry if this is a stupid answer but... can you export the data to a text file, edit the time field formats and then import back?

[Bally Heck]

Gentlemen

The thing to appreciate here is the way Excel treats time. As far as it is concerned, time is a number. One day corresponds to the number one, 12 days to the number twelve, 12 hours is 0.5, and 1 minute is 0.00069444444444444400.

When you add up hours and minutes and the total is greater than 24 hours it will be displayed as just the hours over the whole days. The way to add totals of hours is to allow excel to treat them as numbers and use the sum function to add them into the total.

If you have a series of hours and minutes which add up to say 34 hours 15 minutes, this will display as 10:15 if the cell is formatted HH:MM or as 1.42708333333 etc. etc if it is formatted to display as a number. ie 1.42708333333 days

To display this number as hours and minutes:

Take another cell and enter the formula
"INT(CELL*24)" where CELL is the cell number with the sum of time.

This will display the whole hours.

In an adjacent cell enter the formula
"60*((D5*24)-INT(D5*24))"

This will display the number of minutes.

To display hours and minutes in one cell, use the concatenate function...thus

"CONCATENATE(HOUR CELL,":",MINUTE CELL)

And voila, your total hours and minutes.

This is a bit clumsy and could probably be streamlined if I could be ars*d, but it works and if you hide the intermediate cells then they will be....well....hidden :D

[Icarus]

All good ideas but al clumsy me thinks! No better than the initial post (sorry noted).
Anyway, have tried a few variations on a theme, but guess what, I am also getting funny returns for some values from Excel!
Noted - I will persevere.
Perhaps you can drop me a copy of the worksheet/workbook so I can see how you are working today (format wise etc).
I think the only (solid) solution is going to be a little VB code in there to provide a sexagesimal/decimal conversion/sum/re-conversion.
Guess I am already looking forward to the week-end now! ;)

[Gash Handlin]

Bally Heck,

Read my post on the first page,

You don't have to use ANY formula to display times greater than 24 hours, simply change the format of the cell that displays the result from HH:MM to [H]:MM.

This does not work with microsoft works however, as you can't seem to apply customised formats and have to use one of the five or so they provide, gits :mad: :mad:

(I appreciate this doesn't help with the original problem as you still have to enter the time, seperated by : )

GH

[Noted]

Games over, problem solved. Thanks for all the inputs.

:D :rolleyes: :D :rolleyes: