Need help converting long time calculation to UDF

[JayCie]

Hi All,
I work with a lot of time based worksheets for a 24/7 operation and constantly use the formula:

=IF($E17>$D17,(HOUR($E17)+(MINUTE($E17)/60))-(HOUR($D17)+(MINUTE($D17)/60)),((HOUR($E17)+24)+(MINUTE($E17)/60))-(HOUR($D17)+(MINUTE($D17)/60)))

to calculate times worked where $D17 is start time and $E17 is finish time. Shifts can start and finish the same day ($E17>$D17) or go past midnight ($D17>$E17).

To make matters even more interesting, there are a number of variables that require adding either "and" and/or "or" conditions to this formula so I can end up with several of these nested and it gets pretty complicated.

I'd like to have a UDF so I can just enter the formula:

=CalcTime(Start Time,Finish Time)

I'm pretty new to VBA and have had a go but with no success. Can anyone help?

With thanks
JayCie

 

[andrewman]

Function CalcTime(Start_Time, Finish_Time)

Dim Adjustment As Integer
If Start_Time > Finish_Time Then
Else
Adjustment = 24
End If
CalcTime = Adjustment + Hour(Start_Time) + Minute(Start_Time) / 60 - Hour(Finish_Time) + Minute(Finish_Time) / 60

End Function

 

[wsjackman]

Function CalcTime(Start_Time, Finish_Time)

Dim Adjustment As Integer
If Start_Time > Finish_Time Then
Else
Adjustment = 24
End If
CalcTime = Adjustment + Hour(Start_Time) + Minute(Start_Time) / 60 - Hour(Finish_Time) + Minute(Finish_Time) / 60

End Function

i think you meant

Function CalcTime(Start_Time, Finish_Time)
Dim Adjustment As Integer
If Start_Time > Finish_Time Then Adjustment = 24
CalcTime = Adjustment + Hour(Start_Time) + Minute(Start_Time) / 60 - (Hour(Finish_Time) + Minute(Finish_Time) / 60)
End Function[/QUOTE]

 

[barry houdini]

=IF($E17>$D17,(HOUR($E17)+(MINUTE($E17)/60))-(HOUR($D17)+(MINUTE($D17)/60)),((HOUR($E17)+24)+(MINUTE($E17)/60))-(HOUR($D17)+(MINUTE($D17)/60)))

I'd have to agree that you should only need a short formula. Assuming E17 and F17 are times (i.e. no values greater than 23:59:59) then you should only need a formula like

=MOD(F17-E17,1)*24

The only way that could get different results to your formula is where the times include seconds (because your formula ignores the seconds), so in that case this formula would be sufficient to get identical results:

=MOD(TEXT(E17,"h:mm")-TEXT(D17,"h:mm"),1)*24

 

[andrewman]

I have do some typo. It should be :-

Function CalcTime(Start_Time, Finish_Time)
Dim Adjustment As Integer


If Start_Time > Finish_Time Then
Else
Adjustment = 24
End If


CalcTime = Adjustment + Hour(Start_Time) + Minute(Start_Time) / 60 - Hour(Finish_Time) - Minute(Finish_Time) / 60

End Function

 

[andrewman]

When I am thinking why WSJackman use the mod 1 function in UDF, Barry houdini shown us the simply function. Thus, I know how to use mod 1 now! Thanks Barry!

I sugguest you should use:-

=MOD(TEXT(E17,"h:mm")-TEXT(D17,"h:mm"),1)*24

 

[JayCie]

Hi wsjackman,
I'm not sure how the mod function works with time but it's something I hadn't tho't of and I'm certainly going to test this out. It's going to take some time.

Hi Andrew,
I'm the only user at the moment. As complicated as some of my formulas are, they work, so I'm really looking to smplify them and keep them working.

My workbook consists of 19 worksheets, one has the shift times hard typed in and all the rest update automatically based on data from other worksheets progressively. I use the file everyday so time is an issue as far as modifying it is concerned.

While a UDF for this formula isn't strictly necessary, it would make reading and modifying my formulas much easier than they currently are (and who really likes all those brackets?).
Regards
JayCie

 

[JayCie]

Wow, you guys are fast.
Cells are formatted as [hh]:mm so "0" shows in front of single digit hours - i.e. 09:00 instead of 9:00. Times are always entered as if in one day - i.e., a shift that starts at 10:00pm and ends as 6:00am is entered as start time 22:00 and end time 06:00. Times are never entered as 24:00. A 16:00 to 24:00 shift is entered as 16:00 to 23:59.
JayCie

 

[wsjackman]

Hi wsjackman,
I'm not sure how the mod function works with time but it's something I hadn't tho't of and I'm certainly going to test this out. It's going to take some time.
JayCie

the mod is useful with time-date variables

a day is a unit value f.or example today, which would show as 7/18/2009,

is kept in memory as 40012 (a little over 40000 days since the beginning of the 20th century)

now() entered at 6 am would be 40012.25

using mod ...1 just leaves the 0.25 or the 6 am portion of the date-time

Thus a mod(date-time,1)*24 would give the hours with a decimal equiv to the minutes seconds extra

barry's formula uses the fact that mod(-.4,1)=.6 which allows you to eliminate the if start> end.

if some of your outiside sources for times do have a date component, you will get screwy results and even barry's formula might need mods inside,

=MOD((mod(F17,1)-mod(E17,1),1)*24


his hours-minutes formula would not need this

the format for mod in VBA is different from the worksheet

a new udf based on what I relearned from barry

function calctime(start,stop)
start = start mod 1
stop = stop mod 1
calctime=24 *((start-stop) mod 1)
end function

 

[JayCie]

Hi Barry,
Time is never entered with seconds so it's not an issue. I'll try this.
Thanks
JayCie

 

[JayCie]

Thanks to all of you,
I now have a working UDF:

Function CalcTime(Start, Finish) As Integer
Application.Volatile
If Finish > Start Then
    CalcTime = Hour(Finish) + Minute(Finish) / 60 - Hour(Start) + Minute(Start) / 60
ElseIf Start > Finish Then
    CalcTime = Hour(Finish) + 24 + Minute(Finish) / 60 - Hour(Start) + Minute(Start) / 60
Else
    CalcTime = 0
End If
End Function

I've added the last (0) option so I can use this on my master shift matrix to calculate days off to "0" - without it I got a "value" error.

Thanks for your help.
Regards
JayCie