need help with custom function

mfexcel

Well-known Member
Joined
Jan 8, 2009
Messages
791
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
Joined
Jan 1, 2014
Messages
119
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
Joined
Jan 8, 2009
Messages
791
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
Joined
Apr 18, 2011
Messages
35,576
Office Version
2010
Platform
Windows
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
Joined
Jan 1, 2014
Messages
119
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
Joined
Jan 8, 2009
Messages
791
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
Joined
Jan 1, 2014
Messages
119
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
Joined
Jan 1, 2014
Messages
119
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
Joined
Apr 18, 2011
Messages
35,576
Office Version
2010
Platform
Windows
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.
 

Forum statistics

Threads
1,086,120
Messages
5,387,961
Members
402,090
Latest member
thomastsiakis

Some videos you may like

This Week's Hot Topics

Top