Hi
I saw a post from another member 2 days ago asking for the same thing but when I tried the suggested formula it didn't work.
I have the following date/times in 2 cells (please note the date is dd/mm/yy - I got into all sorts of mess trying to figure out a formula that was based on mm/dd/yy):
G3: 03/01/12 04:26
O3: 03/01/12 09:10
My work day is from 09:00 - 17:30 so I would expect a result of 0:10 for the above example as the start time is outside my working hours. The formula's I have tried are:
=(NETWORKDAYS(G3,O3)-1)*(17.5/24-9/24)+MOD(O3,1)-MOD(G3,1) which returned a result of 4:44
=NETWORKDAYS(G3,O3,0)*("17:30"-"09:00") which returned a result of 8:30
=(NETWORKDAYS(G3,3)-1)*(17.5/24-9/24)+IF(NETWORKDAYS(O3,O3),MEDIAN(MOD(O3,1),17.5/24,9/24),17.5/24)-MEDIAN(NETWORKDAYS(G3,G3)*MOD(G3,1),17.5/24,9/24) which returned a result of ##################
I'm a fairly novice user of excel so please do bear with me - I have no idea what any of the above mean!
Please help!
Also I was just advised to post that the 2 date/times could be within the same day as the example above is or could go over 2 or more days.
Bookmarks