need help with custom function

sumhungl0

Board Regular
Joined
Jan 1, 2014
Messages
119
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.
 
with this criterion, an ordinal formula may not help...

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".

So I will definitely go with Rick's solution! :)
 
Upvote 0

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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.

Code:
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.

holy poop. that is flippin awesome. im pretty sure i can build a formula to bring all the cells in the column to a single cell. ill get this working and play around with it in the morning. i will post back to let you know what i come up with. thank you so much for all the help. you are saving me alot of time. thanks again.
 
Upvote 0
Hi Rick, something went wrong when there 2400 is input...


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.

Code:
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.
 
Upvote 0
Hi Rick, something went wrong when there 2400 is input...
Theoretically, there is no time of 24:00... after 23:59:59 comes the next day starting at 00:00. I should be able to fix the formula, but before doing so, let me find out if there might be another problem on the horizon... will your start time ever be in one day and the end time in another day (so that the end time will "look" to be less than the start time... I am thinking like a start time of 22:00 and an end time of 03:00 for example)?
 
Upvote 0
Theoretically, there is no time of 24:00... after 23:59:59 comes the next day starting at 00:00. I should be able to fix the formula, but before doing so, let me find out if there might be another problem on the horizon... will your start time ever be in one day and the end time in another day (so that the end time will "look" to be less than the start time... I am thinking like a start time of 22:00 and an end time of 03:00 for example)?

That's a good question. I haven't run into anything yet where it ran from one day to the next. So far....... might be worth looking into tho.
 
Upvote 0
Hi Rick, you are right.

Theoretically, there is no time of 24:00... after 23:59:59 comes the next day starting at 00:00

I am just confused why it doesn't work.
By using formula =Text("2400","00\:00")+0, 2400 (and beyond) can be converted to 1 (and (1.sth)) so that difference in time can be computed.
As I am not good in vba, I am just curious the difference in the result? Appreciate your advice.


I have to leave this to the OP to answer you... ;p
will your start time ever be in one day and the end time in another day
 
Upvote 0
perfect. well minus the 2400 hour change over from one day to the next. not really seeing any issues with that because I haven't seen any hours in my sheet that go into the next day. but for now this is working very well. thanks everyone for all the input and thank you rick for the sample code. I had to change it a bit to get hours as a decimal but I figured that out on my own. thanks again everyone, have a great weekend. here is the code I finished up with.
Code:
Function TotalTimeN(S As String) As Double 'same as above but result is hours in decimal format
  Dim X As Long, Parts() As String, t As Date
  Parts = Split(S, "-")
  For X = 0 To UBound(Parts) - 1
    If Right(Parts(X), 4) Like "####" And Left(Parts(X + 1), 4) Like "####" Then
      t = t + CDate(Format(Left(Parts(X + 1), 4), "00\:00")) - CDate(Format(Right(Parts(X), 4), "00\:00"))
      TotalTimeN = TimeValue(t) * 24
    End If
  Next
End Function
 
Upvote 0
one other thing I did come across is the chance that another user of this sheet sometimes will look at the total hours cell and manually add a few hours to the number by typing it in. in this case I will lose my formula, which I forgot to mention in the last post is "=sum(totaltimen(d3),totaltimen(d4),and so on.....)". maybe I could do a beforeupdate or something to take the input value and add the formula to it. thanks everyone.
 
Last edited:
Upvote 0
one other thing I did come across is the chance that another user of this sheet sometimes will look at the total hours cell and manually add a few hours to the number by typing it in. in this case I will lose my formula, which I forgot to mention in the last post is "=sum(totaltimen(d3),totaltimen(d4),and so on.....)". maybe I could do a beforeupdate or something to take the input value and add the formula to it. thanks everyone.

A formula is not of much help if a user can overwrite it. You should unlock all the cells the user is allowed to change, leave the rest locked and the turn protection on for the sheet. If the users need to be able to add hours, then give them a column in which to do that and include that column in the SUM function.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,273
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top