Microsoft Excel  excel time calculation  Asked By usha anu on 23Apr13 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 usha anu on 23Apr13 05:25 AM
Hello usha,
This might help
http://office.microsoft.com/enau/excelhelp/networkdaysHP005209190.aspx
Regards
harry
usha anu replied to Harry Boughen on 24Apr13 02:56 AM
hi harry,
is it possible any macro for this .
thanks
usha
Harry Boughen replied to usha anu on 24Apr13 03:37 AM
Hello Usha,
Anything is possible but why not use a formula?
=(NETWORKDAYS(C2,F2)1)*8.5+HOUR(F2INT(F2)C2+INT(C2))+MINUTE(F2INT(F2)C2+INT(C2))/60
Regards
Harry
usha anu replied to Harry Boughen on 24Apr13 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 usha anu on 24Apr13 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(F2INT(F2)C2+INT(C2))+MINUTE(F2INT(F2)C2+INT(C2))/60
Regards
Harry
usha anu replied to Harry Boughen on 24Apr13 11:35 PM
hi harry
when i put this formula some cases show in  like
asb 
Ok 
1/5/2013 10:55 
Jan13 
ddad 
1/5/2013 13:15 
14.68333333 
thanks
anu
usha anu replied to Harry Boughen on 25Apr13 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 usha anu on 25Apr13 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
usha anu replied to Harry Boughen on 25Apr13 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 usha anu on 25Apr13 02:48 AM
Hello Usha,
I have reworked the formula.
=IF((F2C2)>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.5HOUR(C2INT(C2))+MINUTE(C2INT(C2))/60+HOUR(F2INT(F2))+MINUTE(F2INT(F2))/609,HOUR(F2INT(F2))+MINUTE(F2INT(F2))/60(HOUR(C2INT(C2))+MINUTE(C2INT(C2))/60))
Trust that gives you what you want.
Regards
Harry
usha anu replied to Harry Boughen on 25Apr13 05:24 AM
hi harry thank you for your supprt but sitll in some calculate like:
adsa 
Ok 
1/2/2013 16:23 
Jan13 
asdsad 
1/3/2013 10:10 
6.216666667 
why it`s shows 6.21 hours
thanks
usha
Harry Boughen replied to usha anu on 25Apr13 07:59 AM
Hello Usha,
Try
=IF(OR((F2C2)>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.5HOUR(C2INT(C2))MINUTE(C2INT(C2))/60+HOUR(F2INT(F2))+MINUTE(F2INT(F2))/609,HOUR(F2INT(F2))+MINUTE(F2INT(F2))/60(HOUR(C2INT(C2))+MINUTE(C2INT(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
usha anu replied to Harry Boughen on 26Apr13 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 
Jan13 
erewr 
1/22/2013 17:00 
0.5 
abc 
Ok 
1/24/2013 17:30 
Jan13 
erewr 
1/25/2013 17:05 
0.416666667 
abc 
Ok 
2/3/2013 11:58 
Feb13 
erewr 
2/4/2013 11:00 
0.966666667 
abc 
Ok 
2/3/2013 12:16 
Feb13 
erewr 
2/4/2013 11:45 
0.516666667 
abc 
Ok 
2/10/2013 16:59 
Feb13 
erewr 
2/11/2013 10:35 
6.4 
abc 
Ok 
2/10/2013 17:30 
Feb13 
erewr 
2/11/2013 13:00 
4.5 
abc 
Ok 
2/10/2013 14:44 
Feb13 
erewr 
2/11/2013 13:25 
1.316666667 
abc 
Ok 
2/15/2013 17:27 
Feb13 
erewr 
2/16/2013 17:05 
0.366666667 
abc 
Ok 
2/17/2013 12:53 
Feb13 
erewr 
2/18/2013 11:00 
1.883333333 
abc 
Ok 
2/17/2013 17:30 
Feb13 
erewr 
2/18/2013 11:55 
5.583333333 
thanks
usha
usha anu replied to Harry Boughen on 26Apr13 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 usha anu on 26Apr13 05:55 AM
Hello Usha,
The formula calculates fractional hours, not hours and minutes.
Regards
Harry
Harry Boughen replied to usha anu on 26Apr13 07:46 AM
Hello Usha,
Now try this.
=IF(OR((F2C2)>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.5HOUR(C2INT(C2))MINUTE(C2INT(C2))/60+HOUR(F2INT(F2))+MINUTE(F2INT(F2))/609,HOUR(F2INT(F2))+MINUTE(F2INT(F2))/60(HOUR(C2INT(C2))+MINUTE(C2INT(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 overcomplicated.
Regards
Harry
Harry Boughen replied to usha anu on 26Apr13 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