Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Netwrokdays in VBA
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Netwrokdays in VBA
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" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Netwrokdays in VBA
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 ) On Sep 6, 6:22*pm, Joel wrote: 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" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Netwrokdays in VBA
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" wrote: 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 ) On Sep 6, 6:22 pm, Joel wrote: 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" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Netwrokdays in VBA
Thanks, it works
how should i exclude saturday & sunday On Sep 6, 9:31*pm, Joel wrote: 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" wrote: 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 ) On Sep 6, 6:22 pm, Joel wrote: 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" wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Netwrokdays in VBA
SAee pearson's website
http://www.cpearson.com/Excel/betternetworkdays.aspx YOIu can includee these functions in VBa by using either worksheetfunction or evaluate. "fi.or.jp.de" wrote: Thanks, it works how should i exclude saturday & sunday On Sep 6, 9:31 pm, Joel wrote: 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" wrote: 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 ) On Sep 6, 6:22 pm, Joel wrote: 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" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Netwrokdays in VBA | Excel Worksheet Functions | |||
Netwrokdays and EOMONTH Functions return N/A when opening Excel sh | Excel Worksheet Functions |