Netwrokdays in VBA

[fi.or.jp.de]

Hi All,

I have created userform, I need some help on this !

We have APAC, US regions.

Start date & time AND end date & time captured in userform ( those
data stored in MS Access )

Eg : Start date & time ( mm/dd/yyyy hh:mm:ss )

US region timing 5.30 PM - 2.30 AM ( Indian Standard Time )

If user gets request Within US region time then end minus start -
total time will be captured.

If user get request after US region time - user will update the
userform whatever the time

he received the request. Say 4:30 AM ( IST ) at this time, US Market
closed.

Once the US market open we proceed with our work.

Total time for the above case will be different because request
received after US region time.

Eg,
Received time - 4.00 AM IST
Completed Time - 8.30 PM IST
Total time taken to complete - 0 days 3 hrs ( as it is on the same
day ).

NOTE : there are cases where requested completed after a week also.

So, Answer should be in days & hrs ( only Business Days )

Because Received after US region time Time Starts from 5.30 PM IST.

I Need VBA Soultion.

Thanks in advance.

 

[Joel]

If all the dates and imes are stored as data formats (Not ascii strings) then
subtracting the end time - start time will give a number. The integer part
of the number is the Days and the fractional part is the hours. So this code
should work

StartTime = DateValue("9/7/09") + TimeValue("10:00 AM")
EndTime = DateValue("9/8/09") + TimeValue("5:00 AM")
DeltaTime = EndTime - StartTime

MyDate = Int(DeltaTime)
MyHours = DeltaTime - MyDate

Results = MyDate & " Days and " & Hour(MyHours) & " hours"

 

[fi.or.jp.de]

Thanks for the reply,

But i need like this !!!
Eg.,
Request received 4:00 AM ( request received after cut-off time )
Request Completed : same day 10:00 PM
Answer : 10:00 PM minus 5:30 PM ( 4.30 hrs as US region start time is
5:30 PM )

 

[Joel]

Yuu need to store all your times in the database relative to the same
timezone no matter where the wok is actualy done. You will see at this
website the times are all stored as PST. Once you adjust all time to the
same timezone then you canuse my statements to get you results.

Because you are reference NY time I would adjust all you times to Eastern
Timezone. If you are in India subtract 12 hours. Don't save the data and
time seperately combine them like I did

StartTime = DateValue("9/7/09") + TimeValue("10:00 AM")

Make sure the time includes the data also. Excel doesn't like negative
times and when you move backward from one day to the next excel sometimes
makes calcualtion errors.

If yo are in India and need to subtract 12 hours do this

StartTime = DateValue("9/7/09") + TimeValue("3:00 AM")

EasternTime = StartTime - timevalue("12:00 PM")

Since a day is equal to 1 and a half day equals 0.0 you can simply subtract
one-half

EasternTime = StartTime - 0.5

 

[fi.or.jp.de]

Thanks, it works

how should i exclude saturday & sunday

 

[Joel]

SAee pearson's website

http://www.cpearson.com/Excel/betternetworkdays.aspx

YOIu can includee these functions in VBa by using either worksheetfunction
or evaluate.