# need help with custom function

#### mfexcel

##### Well-known Member
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!

#### sumhungl0

##### Board Regular
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.

#### mfexcel

##### Well-known Member
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.

#### Rick Rothstein

##### MrExcel MVP
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)?

#### sumhungl0

##### Board Regular
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.

#### mfexcel

##### Well-known Member
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

#### sumhungl0

##### Board Regular
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``````

#### sumhungl0

##### Board Regular
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:

#### Rick Rothstein

##### MrExcel MVP
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.