# Need help converting long time calculation to UDF

#### JayCie

##### New Member
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

##### Board Regular
I think you don't need UDF.

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

#### JayCie

##### New Member
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

##### Well-known Member
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

##### Board Regular
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

##### New Member
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

##### Well-known Member
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

Last edited:

#### andrewman

##### Board Regular
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

##### Well-known Member
what kind of inconsitencies do you see?

#### wsjackman

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