Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601

    Unanswered: Custom Date Function

    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.
    Ryan
    My Blog

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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

  3. #3
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    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.
    Ryan
    My Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •