Microsoft Excel - excel time calculation - Asked By usha 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/2026 9:00	1/2/2026 9:47	244200	1/2/2026 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

Hello usha,
This might help
http://office.microsoft.com/en-au/excel-help/networkdays-HP005209190.aspx
Regards
harry

hi harry,
is it  possible any macro  for this .

thanks
usha

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

hi .harry
thank you , one more thing my working  days are monday to saturday , can you edit this formula accordinglly.

thank
anu

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

hi harry

 when i put this formula some cases show in - like

asb

Ok

1/5/2026 10:55

Jan-13

ddad

1/5/2026 13:15

-14.68333333

thanks
anu

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

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

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

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

hi harry thank you for your supprt but sitll in some calculate like:

adsa

Ok

1/2/2026 16:23

Jan-13

asdsad

1/3/2026 10:10

-6.216666667

why it`s shows -6.21  hours

thanks
usha

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

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/2026 11:58	Feb-13	erewr	2/4/2026 11:00	-0.966666667
abc	Ok	2/3/2026 12:16	Feb-13	erewr	2/4/2026 11:45	-0.516666667
abc	Ok	2/10/2025 16:59	Feb-13	erewr	2/11/2025 10:35	-6.4
abc	Ok	2/10/2025 17:30	Feb-13	erewr	2/11/2025 13:00	-4.5
abc	Ok	2/10/2025 14:44	Feb-13	erewr	2/11/2025 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

hi harry

one thing is if my data is

1/1/2026 9:00

dsa

asdsa

1/2/2026 11:00

asdsa

10.5

 now my time should be 10.30 hrs. but it`s shows 10.5

thanks
anu

Hello Usha,
The formula calculates fractional hours, not hours and minutes.
Regards
Harry

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

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