Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing time, by time...YUCK
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing time, by time...YUCK
On Sat, 27 Jun 2009 13:01:01 -0700, Ron
wrote: 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/r... T101172771033 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing time, by time...YUCK
On Sat, 27 Jun 2009 13:24:19 -0700, WallyWallWhackr
<wallywallwhackr@thematrixattheendofthemushroomste m.org wrote: On Sat, 27 Jun 2009 13:01:01 -0700, Ron wrote: 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/r... T101172771033 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing time, by time...YUCK
Have you tried selecting the entire columns for B and D, or at least the part you use, and then FormatCellsNumberCustom 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. "Ron" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing time, by time...YUCK
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 -- Steve "Ron" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing time, by time...YUCK
Just read the subject again. I cannot see anything in your message about summing. What is it that you wish to sum by time? -- Steve "AltaEgo" <Somewhere@NotHere wrote in message ... 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 -- Steve "Ron" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing time, by time...YUCK
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" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing time, by time...YUCK
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" wrote: 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" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing time, by time...YUCK
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" wrote in message ... 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" wrote: 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" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing time, by time...YUCK
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 "Ron" wrote: 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" wrote: 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" wrote in message ... 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing time, by time...YUCK
On Sat, 27 Jun 2009 19:36:01 -0700, Ron
wrote: 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. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing time, by time...YUCK
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 "WallyWallWhackr" wrote: On Sat, 27 Jun 2009 13:24:19 -0700, WallyWallWhackr <wallywallwhackr@thematrixattheendofthemushroomste m.org wrote: On Sat, 27 Jun 2009 13:01:01 -0700, Ron wrote: 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/r... T101172771033 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. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing time, by time...YUCK
They probably feel sorry for me....LOL
"FatBytestard" wrote: On Sat, 27 Jun 2009 19:36:01 -0700, Ron wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help summing time | Excel Worksheet Functions | |||
summing time | Excel Discussion (Misc queries) | |||
summing of time | Excel Worksheet Functions | |||
Summing up time | Excel Worksheet Functions | |||
Summing Time | Excel Discussion (Misc queries) |