Here we go
=(NETWORKDAYS(C2,E2,$J$1:$J$10)-(WEEKDAY(C2,2)<=5)-(WEEKDAY(E2,2)<=5))/24*10
+
(WEEKDAY(C2,2)<=5)*(TIME(18,0,0)-MIN(MAX(D2,TIME(8,0,0)),TIME(18,0,0)))+
(WEEKDAY(E2,2)<=5)*(MAX(MIN(F2-TIME(8,0,0),TIME(10,0,0)),0))
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
Darran said:
8am start, 6pm finish. I have manage to get the result I wanted but having to
use another collum. Basically, using my formula I then divide that fraction
by 24 and put that in the Time format and that gives me what I need. But, if
you can do that in one collum then that would be even better!
Really appreciate this Bob.
Regards
Darran
Bob Phillips said:
To answer that, I need to know what the day start time is and the day end
time. This is in case the times in D2 and./or F2 is outside of those hours.
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
Marvellous, never doubted you Bob! Only problem though is that my formula
was
based on a 10 hour day. What abouts in your sum do I need to make those
changes?
(maybe should of mentioned that earlier!).
:
My formula works for that, just format the cell as [h]:mm to allow for
more
than 1 day
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
Well it doesnt take much to confuse me! My formula does give me the
result
in
hours not days - 3.42 is just under 3 and a half hours but what I was
hoping
was possible was that this could be displayed in the H:MM:SS format.
:
I think you are getting confused by what is days and what is hours.
Why would you want to represent 3.5 (very nearly) days as 03:35:00.
Is this any better for you
=(NETWORKDAYS(C2,E2,$J$1:$J$10)-(WEEKDAY(C2,2)<=5)-(WEEKDAY(E2,2)<=5))+
((WEEKDAY(C2,2)<=5)*(1-D2)+
(WEEKDAY(E2,2)<=5)*F2)
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
=((NETWORKDAYS(C254,E254,$J$1:$J$10)-1)*10)+((F254 -D254)*24)
I was hoping for 3.42 to turn into 03:25:00 or thereabouts.
:
Isn't that because 3.42 is 82:19:12. .42 is just left than half
a
day,
just
less than 12 hours? What value did you expect?
What is your formula?
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing
direct)
Thanks Bob but unfortunatley that turned 3.42 into 82:00:00!
:
Format as [H]:MM:SS
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing
direct)
Is it possible to format the results from a networkday
query
to
display in
the TIME format. For example all my results come through
as
10.43,
9.02
etc
can these be changed to actually show the result in
HH:MM:SS?
simply
changing
the format gives out the wrong data (10.4 hours becomes
09:37:52
or
something
similar like that?). Any help will as always be greatfully
received.
