Microsoft Excel - excel time calculation - Asked By anu anu on 23-Apr-13 03:21 AM

hi,

 i have some data in excel worksheet 1, like below

Branch Challan Number Creation Date Issued Date abc Delivery Date And Time Customer Name Ticket Staus
abc 4.46413E+11 1/1/2013 9:00 1/2/2013 9:47 244200 1/2/2013 10:20 xyz  

now i need calculate hours(Ticket staus column
0 from delivery date - Creation date with condition working hours 9:00 Am To 17:30 pm and saturday and Sunday off.

thanks
Anu
Harry Boughen replied to anu anu on 23-Apr-13 05:25 AM
Hello usha,
This might help
http://office.microsoft.com/en-au/excel-help/networkdays-HP005209190.aspx
Regards
harry
anu anu replied to Harry Boughen on 24-Apr-13 02:56 AM
hi harry,
is it  possible any macro  for this .

thanks
usha
Harry Boughen replied to anu anu on 24-Apr-13 03:37 AM
Hello Usha,
Anything is possible but why not use a formula?
=(NETWORKDAYS(C2,F2)-1)*8.5+HOUR(F2-INT(F2)-C2+INT(C2))+MINUTE(F2-INT(F2)-C2+INT(C2))/60
Regards
Harry
anu anu replied to Harry Boughen on 24-Apr-13 07:06 AM
hi .harry
thank you , one more thing my working  days are monday to saturday , can you edit this formula accordinglly.

thank
anu
Harry Boughen replied to anu anu on 24-Apr-13 08:25 AM
Hello Usha,
In your first post you indicated that Sat and Sun were not included.
This is a bit clunky but I think it works.
=(NETWORKDAYS(C2,F2)-1-(WEEKDAY(F2)>WEEKDAY(C2))+ROUNDUP((NETWORKDAYS(C2,F2)-1)/5,0))*8.5+HOUR(F2-INT(F2)-C2+INT(C2))+MINUTE(F2-INT(F2)-C2+INT(C2))/60
Regards
Harry
anu anu replied to Harry Boughen on 24-Apr-13 11:35 PM
hi harry

 when i put this formula some cases show in - like

asb Ok 1/5/2013 10:55 Jan-13 ddad 1/5/2013 13:15 -14.68333333


thanks
anu
anu anu replied to Harry Boughen on 25-Apr-13 12:14 AM
hi harry,

one more thing if creation time is 12: pm and  dilivery time is after 17:30 like 19:30 pm. than  is this formula is working

thanks
Anu
Harry Boughen replied to anu anu on 25-Apr-13 12:21 AM
Hello Usha,
The formula was based on it being a 8.5 hour working day.  It will not take into account out of hours delivery.  That will take a whole different approach.  Is that what you want?
I will have a look at the other problem that you raised.
Regards
Harry
anu anu replied to Harry Boughen on 25-Apr-13 02:18 AM
hi harry,

 yes i want if divery date is not same of creation date then calucation of 8.5 working hors and if creation date and  dilivery date are same then simple calculatin creation -dilivery .

thanks
usha
Harry Boughen replied to anu anu on 25-Apr-13 02:48 AM
Hello Usha,
I have reworked the formula.
=IF((F2-C2)>1,(NETWORKDAYS(C2,F2)+INT(DATEDIF(C2,F2,"d")/7)+OR(WEEKDAY(C2)=7,WEEKDAY(F2)=7)+(WEEKDAY(F2)<WEEKDAY(C2))-2)*8.5+17.5-HOUR(C2-INT(C2))+MINUTE(C2-INT(C2))/60+HOUR(F2-INT(F2))+MINUTE(F2-INT(F2))/60-9,HOUR(F2-INT(F2))+MINUTE(F2-INT(F2))/60-(HOUR(C2-INT(C2))+MINUTE(C2-INT(C2))/60))
Trust that gives you what you want.
Regards
Harry
anu anu replied to Harry Boughen on 25-Apr-13 05:24 AM
hi harry thank you for your supprt but sitll in some calculate like:

adsa Ok 1/2/2013 16:23 Jan-13 asdsad 1/3/2013 10:10 -6.216666667

why it`s shows -6.21  hours

thanks
usha
Harry Boughen replied to anu anu on 25-Apr-13 07:59 AM
Hello Usha,
Try
=IF(OR((F2-C2)>1,(HOUR(F2)<HOUR(C2))),(NETWORKDAYS(C2,F2)+INT(DATEDIF(C2,F2,"d")/7)+OR(WEEKDAY(C2)=7,WEEKDAY(F2)=7)+(WEEKDAY(F2)<WEEKDAY(C2))-2)*8.5+17.5-HOUR(C2-INT(C2))-MINUTE(C2-INT(C2))/60+HOUR(F2-INT(F2))+MINUTE(F2-INT(F2))/60-9,HOUR(F2-INT(F2))+MINUTE(F2-INT(F2))/60-(HOUR(C2-INT(C2))+MINUTE(C2-INT(C2))/60))
This, I know, misses out if the delivery is exactly one day later than the start time but I don't have time at the moment to solve that one.
Regards
Harry
anu anu replied to Harry Boughen on 26-Apr-13 04:44 AM
hello harry

 thanks  foe your support but when ever you have time please try to solve it , now i have less calculation which is not calculating correctlly

 
abc Ok 1/21/2013 17:30 Jan-13 erewr 1/22/2013 17:00 -0.5
abc Ok 1/24/2013 17:30 Jan-13 erewr 1/25/2013 17:05 -0.416666667
abc Ok 2/3/2013 11:58 Feb-13 erewr 2/4/2013 11:00 -0.966666667
abc Ok 2/3/2013 12:16 Feb-13 erewr 2/4/2013 11:45 -0.516666667
abc Ok 2/10/2013 16:59 Feb-13 erewr 2/11/2013 10:35 -6.4
abc Ok 2/10/2013 17:30 Feb-13 erewr 2/11/2013 13:00 -4.5
abc Ok 2/10/2013 14:44 Feb-13 erewr 2/11/2013 13:25 -1.316666667
abc Ok 2/15/2013 17:27 Feb-13 erewr 2/16/2013 17:05 -0.366666667
abc Ok 2/17/2013 12:53 Feb-13 erewr 2/18/2013 11:00 -1.883333333
abc Ok 2/17/2013 17:30 Feb-13 erewr 2/18/2013 11:55 -5.583333333


thanks
usha
anu anu replied to Harry Boughen on 26-Apr-13 05:43 AM
hi harry

one thing is if my data is

1/1/2013 9:00 dsa asdsa 1/2/2013 11:00 asdsa 10.5
 now my time should be 10.30 hrs. but it`s shows 10.5

thanks
anu
Harry Boughen replied to anu anu on 26-Apr-13 05:55 AM
Hello Usha,
The formula calculates fractional hours, not hours and minutes.
Regards
Harry
Harry Boughen replied to anu anu on 26-Apr-13 07:46 AM
Hello Usha,
Now try this.
=IF(OR((F2-C2)>1,(HOUR(F2)<HOUR(C2))),(NETWORKDAYS(C2,F2)+INT(DATEDIF(C2,F2,"d")/7)+OR(WEEKDAY(C2)=7,WEEKDAY(F2)=7)+(WEEKDAY(F2)<WEEKDAY(C2))-2)*8.5+17.5-HOUR(C2-INT(C2))-MINUTE(C2-INT(C2))/60+HOUR(F2-INT(F2))+MINUTE(F2-INT(F2))/60-9,HOUR(F2-INT(F2))+MINUTE(F2-INT(F2))/60-(HOUR(C2-INT(C2))+MINUTE(C2-INT(C2))/60)+(DAY(F2)-DAY(C2))*8.5)
I will try to have a think about this some more when I get time to try to get a simpler algorithm.  This has sort of grown like topsy and is quite possibly over-complicated.
Regards
Harry
Harry Boughen replied to anu anu on 26-Apr-13 08:10 AM
Hello Usha,
Some of the calculation errors in your last table are also due to the fact that some of the start dates are a Sunday.
Regards
Harry