need help with custom function

[sumhungl0]

starting from scratch, I need to come up with a custom function to get cells with key words in a column and add up hours to get me the total hours for each day(column). here is how the sheet is set up. column a,b&c are frozen and contain equipment id and general info about the equipment(forklifts and material handling equip). across the top row are the dates. so this is a continuous schedule for equipment usage and maintenance. the corresponding cell under the date column and in the equipment row is where we schedule an action for the equipment for that day. it can be maintenance action or usage. im not concerned with maintenance but I need to get the hours in which all equipment was used for each day(column). so the example data that im looking for is like this "AAT 0930-1300". that means that section AAT will be using the equipment from 930am to 1pm. hope that my explanation is clear, if not just ask, please. ok so now here is what I would like to accomplish. at the bottom of each column I would like to get a total of usage for that day. so I need a custom function to find these cells in the column that contain key words, meaning find the section abbreviation code like the example above would be "AAT". and there are about 30 er so sections who use the equipment. then after the key word/section is found in a cell I need to get the time frame and subtract to get the hours used from it. the custom function will need to find the time frame in the same format as the example above"0930-1300". four numbers, dash, four numbers. cells can have other numbers in them, but time frame for usage is always in this "0000-0000" format. does anybody have any ideas on how I can get this done? or maybe point me in the right direction? thanks in advance for your help.

 

[bschwartz]

Can you post some sample data please?

 

[mfexcel]

What you need is to extract the start time and end time from your test string. Given your data follows specific format, it should be done by using MID (for start time) and LEFT (for end time).
Then you may convert the four-digit number (text) into a real time in Excel, so that you may perform calculation End-Start to get the duration.
Here's an article for your reference.
Time Conversion (2) | wmfexcel


Should you need more help, post some data and your problem that you encountered.

the custom function will need to find the time frame in the same format as the example above"0930-1300". four numbers, dash, four numbers. cells can have other numbers in them, but time frame for usage is always in this "0000-0000" format. does anybody have any ideas on how I can get this done? or maybe point me in the right direction? thanks in advance for your help.

 

[sumhungl0]

Can you post some sample data please?

sample data would be like this: example1 is only usage "ATT 0930-1300". example2 is usage and maintenance "ELR 0800-0900 60 day oil change" or "60 day oil change ELR 0800-0900". so maybe I just need a function to read only numbers in this format "0000-0000". what do you think?

 

[sumhungl0]

What you need is to extract the start time and end time from your test string. Given your data follows specific format, it should be done by using MID (for start time) and LEFT (for end time).
Then you may convert the four-digit number (text) into a real time in Excel, so that you may perform calculation End-Start to get the duration.
Here's an article for your reference.
Time Conversion (2) | wmfexcel


Should you need more help, post some data and your problem that you encountered.

ok that is kinda what im looking for, but I don't know vba very much and im here to get some help with that. the end result is a single cell at the bottom of each column that will show the hours used for that day. I don't know where to start. do you have a sample that will extract numbers in that format "0000-0000"?

 

[Rick Rothstein]

sample data would be like this: example1 is only usage "ATT 0930-1300". example2 is usage and maintenance "ELR 0800-0900 60 day oil change" or "60 day oil change ELR 0800-0900". so maybe I just need a function to read only numbers in this format "0000-0000". what do you think?

1) Is your time interval always located after the first space in the text?

2) Is the code before the time interval always three characters long?

 

[sumhungl0]

no and no. 1) the time interval will be after the section code and space, yes. but that might not be the only thing in that cell and there might be twice in a cell in which there are time intervals. so for a cell like that I would need to get both intervals for usage. example "AAP 0900-1000 RAV 1200-1330". so I think the real way to make this happen is to just look for all numbers in that format "0000-0000". what do you think? and 2) the codes are from 2 to 4 alpha-numeric. so it can be AA, AAA, AAAA or AAA1. thanks for your help.

 

[mfexcel]

I don't know vba very much either. ;p
I think you problem can be solved by formula, which requires your input to Rick's questions.

Nevertheless, I would suggest you use helper columns to break your original data into different columns, e.g. Usage, Start Time, End Time. Then you may get the duration and subsequently the total duration. In this way, you may easily manage and understand your spreadsheet, I believe.

ok that is kinda what im looking for, but I don't know vba very much and im here to get some help with that. the end result is a single cell at the bottom of each column that will show the hours used for that day. I don't know where to start. do you have a sample that will extract numbers in that format "0000-0000"?

 

[sumhungl0]

I agree, however, this sheet doesn't belong to me and I already told them that and the bosses that look at this sheet do not want me to change things too much on it. there is already a cell at the bottom of each column for total hours but I have to manually input those values. I was hoping to get this to happen automatically. that is why im asking here. this schedule is large and is planned out for years(currently extends out to 2018), so there is a column for each day for that long. any other ideas? thanks again for helping me.

 

[Rick Rothstein]

no and no. 1) the time interval will be after the section code and space, yes. but that might not be the only thing in that cell and there might be twice in a cell in which there are time intervals. so for a cell like that I would need to get both intervals for usage. example "AAP 0900-1000 RAV 1200-1330". so I think the real way to make this happen is to just look for all numbers in that format "0000-0000". what do you think? and 2) the codes are from 2 to 4 alpha-numeric. so it can be AA, AAA, AAAA or AAA1. thanks for your help.

The following UDF will calculate the total time represented by all time intervals in the text passed into its argument, so to call it from a single cell...

=TotalTime(A1)

You will need to format the cell with the formula using the Time display of your choice.

Function TotalTime(S As String) As Date
  Dim X As Long, Parts() As String
  Parts = Split(S, "-")
  For X = 0 To UBound(Parts) - 1
    If Right(Parts(X), 4) Like "####" And Left(Parts(X + 1), 4) Like "####" Then
      TotalTime = TotalTime + CDate(Format(Left(Parts(X + 1), 4), "00\:00")) - CDate(Format(Right(Parts(X), 4), "00\:00"))
    End If
  Next
End Function

Once you have copied the formula down through your data, you can sum the formula cells to get a grand total.