Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time Calculations using VBA.
Hi Excel Gurus,
I want to use Excel VBA to calculate daily working time. Both Start time and End time have to go by quarters of hours. It means that Starting at 09:05 is equivalent to 09:00. at 09:08 will be 09:15 etc. Since I'm a novice to Excel, I have some difficulties to add/subtract minutes from Time(). Then I have to subtract one (1) hour from the total time, for lunch break. How do I manipulate times in Excel? Thanks Eric |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time Calculations using VBA.
Dates and times are stored in excel as a number, starting at 1/1/1900 as
) and incrementing by 1 each day. Therefore an hour is = 1/24 or 0.041666667 of a day. Today 5/1/2004 is 37991. As time is a number you can simply adda nd subtract them as for any other number. Please note however that when Excel dispays a number, it assumes by defult that the time is to be showing as a portion of 24 hours. So 32 hours(or 1.333333333 as a numebr) is displayed as 8:00 not 32:00. To display the time in hours for periods greater than 24, amend the cell custom format to read [h]:mm. ( the usual cell format is h:mm). The [] instruct eexel to show the full hours. Hopefully that can get you started with times. --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time Calculations using VBA.
Thanks Kieran and Rob.
Rob you saved me the main part of rounding the time. Do you know how to subtract 1 hour for lunch from the time (without it I cannot take my lunch break)? And again thanks to you guys. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time Calculations using VBA.
Eric,
If I'm assuming the non-complicated solution, then you just subtract 1 hour from the duration: Sub testit() Dim dtmStart As Date, dtmEnd As Date, dblDuration As Double dtmStart = "1-Jan-2004 09:05" dtmEnd = "1-Jan-2004 17:19" dtmStart = Round(dtmStart * 24 * 4) / 4 / 24 dtmEnd = Round(dtmEnd * 24 * 4) / 4 / 24 dblDuration = dtmEnd - dtmStart - 1 / 24 MsgBox Format(dblDuration, "hh:mm") End Sub I like to think of a date datatype as just a number in disguise. Rob "Eric" wrote in message ... Thanks Kieran and Rob. Rob you saved me the main part of rounding the time. Do you know how to subtract 1 hour for lunch from the time (without it I cannot take my lunch break)? And again thanks to you guys. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time Calculations using VBA.
Eric,
Sub testit() Dim dtmStart As Date dtmStart = "1-Jan-2004 09:05" dtmStart = Round(dtmStart * 24 * 4) / 4 / 24 End Sub Rob "Eric" wrote in message ... Hi Excel Gurus, I want to use Excel VBA to calculate daily working time. Both Start time and End time have to go by quarters of hours. It means that Starting at 09:05 is equivalent to 09:00. at 09:08 will be 09:15 etc. Since I'm a novice to Excel, I have some difficulties to add/subtract minutes from Time(). Then I have to subtract one (1) hour from the total time, for lunch break. How do I manipulate times in Excel? Thanks Eric |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time Calculations using VBA.
Hello, Rob van Gelder and any other VBA afficionados out there! That
was a clever solution to the date/time workday calculation question you provided. Could I ask about a somewhat different scenario? Instead of Excel, my situation is in Access. I have two date/time fields, one for Start and one for Finish. I am trying to calculate net workday work hours. If a trouble ticket is not resolved (Finish) on the same day, I need to calculate how many 8a-5p Mon-Fri workday hours elapsed. Looks like Excel has a NetWorkday function. Would it be easiest for me to import my Access data into Excel, and attempt to use Excel? THANK YOU. ..Clay *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time Calculations using VBA.
I don't think I ever got to writing the solution. Just managed to describe
the solution. As I recall, it was for ambulance resource management. Norman Harker provided the solution offline. Perhaps, if he's listening, he can provide you the quick answer? I will be posting a solution within the next few days and if I ever get around to putting a website up.... well... The logic goes a little like this: Think about the 4 events. A, B, C, D. A = Start Time B = End Time C = Interval Start D = Interval End Figure out the permutations (I found John Walkenbach's VBA routine handy for this: http://www.j-walk.com/ss/excel/tips/tip46.htm) You'll get 24 combinations, but assuming C always occurs before D then 12 of those can be eliminated. It's strange to think that B could occur before A, but times don't have a date. So while it's possible a troubleticket might have been (A, B) from 1-Jan-2004 14:00 to 3-Jan-2003 09:00, the times we compare are without the date. Stay tuned. Rob "Clay Watson" wrote in message ... Hello, Rob van Gelder and any other VBA afficionados out there! That was a clever solution to the date/time workday calculation question you provided. Could I ask about a somewhat different scenario? Instead of Excel, my situation is in Access. I have two date/time fields, one for Start and one for Finish. I am trying to calculate net workday work hours. If a trouble ticket is not resolved (Finish) on the same day, I need to calculate how many 8a-5p Mon-Fri workday hours elapsed. Looks like Excel has a NetWorkday function. Would it be easiest for me to import my Access data into Excel, and attempt to use Excel? THANK YOU. .Clay *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time Calculations using VBA.
Eric,
I'm working on the same sort of project. May I ask if you got it to work could I get a look at the solutions you came up with ? This time thing is really getting to me. wssparky______________________ We learn by doing, and doing and doing *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time calculations for Scheduled vs Actual Employee Time and Wages | Excel Discussion (Misc queries) | |||
Time calculations for Scheduled Time vs. Actual Time Worked | Excel Discussion (Misc queries) | |||
Time calculations for Scheduled vs Actual Employee Time and Wages | Excel Discussion (Misc queries) | |||
Time difference calculations, daylight savings time, Excel | Excel Discussion (Misc queries) | |||
convert time imported as text to time format for calculations | Excel Worksheet Functions |