1. Registered User
Join Date
Jun 2004
Location
Seattle, WA
Posts
601

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.

2. Registered User
Join Date
Apr 2004
Location
Derbyshire, UK
Posts
805
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. Registered User
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.

#### Posting Permissions

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