Custom Date Function

rguy84 · #1 (**permalink**) 05-18-11, 22:05

I haven't been here for years. Hi to the ones that may remember me.

I need some help taking a formula I created and turning it into a function. After attempting a few times, I decided I needed help.

My formula looks like:

Code:

=IF(TEXT(endTime-startTime,"h:mm")<="1:00",
IF(TEXT(endTime-startTime,"h:mm")="1:00","1 hour",Minute(endTime-startTime)&"min"),
TEXT(endTime-startTime,"h"&" \hour\s "&"mm"&"\m\i\n"))

I am not sure if I need to cast the format that much. The formula checks if the start and end times are greater than one hour. If it isn't, see if the period is 0-59 min, if so say xx min. If the period is exactly 1 hour, print "1 hour". If the period is greater than 1 hour, print x hours y minutes.

I am using excel 2010 if it means anything.

MikeTheBike · #2 (**permalink**) 06-03-11, 10:10

Hi

It seems to work OK for me (Offoce 2003 not 2010 if that makes a difference!)

I made a couple of mods by adding spaces an removing the leading 'm' in the formatting.

=IF(TEXT(endTime-startTime,"h:mm")<="1:00",IF(TEXT(endTime-startTime,"h:mm")="1:00","1 hour",MINUTE(endTime-startTime)&" min"),TEXT(endTime-startTime,"h"&" \hour\s "&" m "&"\m\i\n"))

If you intend to write a user defined function to do this (as you have intimated) then perhaps something like this

Code:

Function TimeDiff(ByVal EndTime As Date, ByVal StartTime As Date) As String
    If Abs((EndTime - StartTime) / (1 / 24)) < 1 - 1 / 60 / 60 Then
        TimeDiff = Format((EndTime - StartTime) * 24 * 60 - 0.5, "#0") & " Min"
    ElseIf Abs((EndTime - StartTime) / (1 / 24)) > 1 + 1 / 60 Then
        TimeDiff = Format((EndTime - StartTime), "h \hour\s m \mi\n")
    Else
        TimeDiff = "1 hour"
    End If
End Function

??

MTB

rguy84 · #3 (**permalink**) 06-03-11, 16:58

Mike, I dropping the "xx min" , "1 hour", "y hrs xx mins" part, because when you go to total it, the value is not 1 it is "1 hour". So you would need to convert it back to time, so, I ended up doing:

Code:

function calcTime(startTime as date, endTime as date)
  calcTime = endTime - startTime
end function

The headache this caused by doing it conditionally wasn't worth it.

Internet Services

Web Development

Digital Marketing

Consumer Tech