Need help converting long time calculation to UDF

JayCie

New Member
Joined
Oct 9, 2008
Messages
18
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
 

JayCie

New Member
Joined
Oct 9, 2008
Messages
18
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
Joined
Jun 19, 2008
Messages
2,465
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
Joined
Jul 16, 2008
Messages
206
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
Joined
Oct 9, 2008
Messages
18
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
Joined
Jun 19, 2008
Messages
2,465
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
Joined
Jul 16, 2008
Messages
206
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
Joined
Jun 19, 2008
Messages
2,465
since the cells are formatted with the [h], do you ever have greater than 23 in them?
 

Forum statistics

Threads
1,082,438
Messages
5,365,531
Members
400,837
Latest member
ELMST616

Some videos you may like

This Week's Hot Topics

Top