Summing time, by time...YUCK

[Ron]

I need to sum the length of tim in column d: according to the hour in column
B:.

Column B = 8:44:00AM or 8:44AM (from a text file, general format.)
Column D - 16:38, stored as 4:38:00 PM (formatted h:mm, but actually min/sec)

I have already changed B to military with:
VALUE(IF(ISNA(CONCATENATE(LEFT(B3,LEN(B3)-2),"
",RIGHT(B3,2))),"",CONCATENATE(LEFT(B3,LEN(B3)-2)," ",RIGHT(B3,2)))) (Not my
formula, but the damn thing works)

B: is sorted so the hours are in order. so I'm sure there is a better way
than pasting about 2000 formulas in a spreadsheet>

Thanks,

Ron

 

[WallyWallWhackr]

I need to sum the length of tim in column d: according to the hour in column
B:.

Column B = 8:44:00AM or 8:44AM (from a text file, general format.)
Column D - 16:38, stored as 4:38:00 PM (formatted h:mm, but actually min/sec)

I have already changed B to military with:
VALUE(IF(ISNA(CONCATENATE(LEFT(B3,LEN(B3)-2),"
",RIGHT(B3,2))),"",CONCATENATE(LEFT(B3,LEN(B3)-2)," ",RIGHT(B3,2)))) (Not my
formula, but the damn thing works)

B: is sorted so the hours are in order. so I'm sure there is a better way
than pasting about 2000 formulas in a spreadsheet>

Thanks,

Ron

http://office.microsoft.com/search/redir.aspx?assetid=TC300060381033&respos=114&pid=CT101172771033

Standard sum functions work with time as the time is ALWAYS stored
internally as a single number by Excel, regardless of how you make it
appear on the sheet, so you likely do not need to go through all of that
if you simply use input validation functions to force the user to input
the correct data to begin with.

Take a look at the sheet. Use whatever you want from it. Enjoy.

 

[WallyWallWhackr]

http://office.microsoft.com/search/redir.aspx?assetid=TC300060381033&respos=114&pid=CT101172771033

Standard sum functions work with time as the time is ALWAYS stored
internally as a single number by Excel, regardless of how you make it
appear on the sheet, so you likely do not need to go through all of that
if you simply use input validation functions to force the user to input
the correct data to begin with.

Take a look at the sheet. Use whatever you want from it. Enjoy.

You could create an input engine that parses the text file line-by-line
to fill across several cells, then concatenate it back together into a
single, final cell, then fill that data into your column B as a properly
formatted value, step to the next line in the text file and continue.
Then, you would not need to have a formula array, which you seem to be
against, even though that is commonly the way it is done.

The engine would fill out your data properly from the text input.
There are likely already such little code based "engines" already written
out there, but I do not know as I am unfamiliar with the community at
that level.

 

[JLGWhiz]

Have you tried selecting the entire columns for B and D, or at least the
part you use, and then Format>Cells>Number>Custom to make them the same
format type. Then you do not need any formulas. All times are stored as
floating point numbers, the trick is to make sure they are the same data
type in the places they are used. If one is text and the other is
date(time) then you will encounter problems. So you can either make
everything a string data type or a date data type.

 

[AltaEgo]

Try the formula =Timevalue(B3)

If you wish to do it in VBA, Timevalue will also work

Sub txt2time()
For Each c In Selection
c.Value = TimeValue(c.Value)
Next c
End Sub

 

[AltaEgo]

Just read the subject again. I cannot see anything in your message about
summing. What is it that you wish to sum by time?

 

[Patrick Molloy]

time in Excel is saved as decimal parts of a day. so 12 noon is 0.5 and 6am
is 0.25
so 08:44 is 0.363888889

use the HOUR() function and MINUTE() functions to split out those parts
separately

I don't get how you want to sum these. please could you re-phrase the
question?

 

[Ron]

Sorry I wasn't clear guys. I hate dealing with time and all it's formats. I
am now 99% there, but I'm sure your ideas will be better.

100 _ rows, col B: has TOD of occurrence. Col D: has length of occurrence.
They are sorted by TOD, 8AM to 8PM.

I need to go down B: and sum all the length of occurrences in column D:.
corresponding to, say, 8AM, then continue down summing column D; for 9AM, etc.

There may be 5 occurences at 8AM and 50 at 9AM and any variation in between.

I then need to put the total length of occurrences (column D at 8AM in a
cell, then the total length of occurrences at 9AM in a different cell.

Column B: will be in the format of 8:44:00AM , 8:56:23AM, etc so all
occurrences that happen in the 8 o'clock hour is considered 8AM.

Hope this helps. I'm rushing to get it done without help, other than
previous postings, then see how it should be done!!!!

I appreciate the hints above. They always add some knowledge I dodn't know
before.

Thanks,

Ron

 

[Patrick Molloy]

so all times between 08:00 and 08:59 are 8AM amd all 09:00 to 09:59 are 9pm

so add a column using the Hour() function, then use COUNTIF()

so lets say B1 is 08:44
in C1 put =HOUR(B1) and replicate down for every item in column B
now C is a list of hours only.

now in F4 to F16 out the numbers 8 throu 20,
and in G4 put =COUNTIF(C1:C100,F4)

 

[Ron]

Further info: I get the data by running Unix scripts on my PC, grepping the
data and sending it through a couple of stream editor commands. I now have
the TOD stripped from 10:34:45AM to 8AM or 8PM, etc.

I then do SUMIF($B$1:$B$600,"8AM",$D$1:$D$600) 12 times to sum my column D:
That means I need my macro to insert 12 formulas (above, for each hour) and
that works, but I'm sure you guys can do better.

You see, I am a butcher. I can take code and revise it and make it work for
me, but starting from scratch is alien to me. I haven;t had enough (any)
programming to get that proficient.

Again, thanks for all the help.

Ron

 

[FatBytestard]

You see, I am a butcher. I can take code and revise it and make it work for
me, but starting from scratch is alien to me. I haven;t had enough (any)
programming to get that proficient.

Again, thanks for all the help.

Ron

Funny. When I made a similar remark, they discontinued assistance.

 

[Ron]

I appreciate the info. I got my sheet working. It's summing fine. Just have
to make sure format s correct for what I'm doing.
I also use those same figures in general format for finding rages....i.e.
this is between 1200 and 1300...etc.

You guys always provide a lot of helps and hints, which help immensly. I'm
trying to wean myself, but there is always a bigger mountain to climb.

Ron

 

[Ron]

They probably feel sorry for me....LOL

Funny. When I made a similar remark, they discontinued assistance.