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]

I think you don't need UDF.

=(IF($E17>$D17,0,24)+($E17-$D17)*24)

 

[JayCie]

Thanks Andrew,
I've tried a lot of 'shorter' formulas with inconsistent results - mostly with shifts that go past midnight and the ones with the variables. I haven't worked out why the results are inconsistent but the hour/minute calculation is the only one so far that gives the right result consistently.

I'll try your formula = I do like nice, clean formulas - but I'd still like a UDF for my long formula just in case. I'll even put up my try at writing it if anyone really wants me to blatantly demonstrate my ignorance.
Regards
JayCie

 

[wsjackman]

assuming the times are times only and not date-time combos

=(($E17<$D17)+($E17-$D17))*24

if they are date-time combos

=($E17-$D17)*24<!-- / message --><!-- sig -->

 

[andrewman]

Dear WSJackMan,

We can add the text function like :-

=(IF(TEXT($E17,"hh:mm")>TEXT($D17,"hh:mm"),0,24)+(TEXT($E17,"hh:mm")-TEXT($D17,"hh:mm"))*24)

 

[JayCie]

Thanks wsjackman,
Times are time (not date/time) in 24 hour format. Start & finish time cells are formated as [hh]:mm. Results are number format with one decimal place and wll always be 7.6, 8, 9.5 or 10 for regular shifts - could be anything for exceptions.
Regards
JayCie

 

[wsjackman]

if you have inconsistencies there may be some mixed time, date time entries

try

=((mod($E17,1) < mod($D17,1)+(mod($E17,1)-mod($D17,1)))*24


function calctime(start,stop)
start = start mod 1
stop = stop mod 1
if start > stop then
calctime = 24 -(start-stop)*24
else
calctime = (stop-start)*24
end function

 

[andrewman]

Dear JayCie,

Do I need to demonstrate how to use the UDF? But, I really don't want to UDF in your case! It is because UDF needs to open the seurity setting of VBA. Your user may not know how to open it.

 

[wsjackman]

what kind of inconsitencies do you see?

 

[wsjackman]

since the cells are formatted with the [h], do you ever have greater than 23 in them?