angus_young_acdc
asked on
Outlook Forms - Calculating Time Spent At Work?
I am working on a form to calculate time spent at work, based on when the person started (8am), when they finish (5pm), and how long they have for lunch(1hour).
I have my calculations to do work out the total time spent at work, however the difficulty comes with the time for lunch. If a person decides to take only 30mins for lunch then their total time worked should increase, however I don't know what type/property to use for this? Duration seems to just give hours, and modifying it in Date/Time means that 1:00 becomes 00:30 (half 12 at night). It is all in the 24hour clock.
Does anybody know what I can use as the most appropriate type/property in order to subtract the time taken for lunch from the total hours worked?
I have my calculations to do work out the total time spent at work, however the difficulty comes with the time for lunch. If a person decides to take only 30mins for lunch then their total time worked should increase, however I don't know what type/property to use for this? Duration seems to just give hours, and modifying it in Date/Time means that 1:00 becomes 00:30 (half 12 at night). It is all in the 24hour clock.
Does anybody know what I can use as the most appropriate type/property in order to subtract the time taken for lunch from the total hours worked?
ASKER
Hi BlueDevilFan
Where would I declare the variable of WorkingTime? Currently I am currently attempting validation inside a textbox control where it is equal to FinishTime - StartTime - LunchTimeTaken
Where would I declare the variable of WorkingTime? Currently I am currently attempting validation inside a textbox control where it is equal to FinishTime - StartTime - LunchTimeTaken
Are you using the "Calculate this formula automatically" option? If so, then you should be able to code that in the formula box.
ASKER
Yes I am. When I change the Field to a custom one, in the textbox that I wish the value to appear I get the following error:
Error in Initial Value formula for "MondayDuration" -- Unknown field.
MondayDuration being the total time worked (end time - start time - lunch).
Error in Initial Value formula for "MondayDuration" -- Unknown field.
MondayDuration being the total time worked (end time - start time - lunch).
Is MondayDuration a field (not a control, a field)? If not, then the error is expected. If end time, start time, and lunch are fields or the name of controls, then the calculation should read something like
endtime - starttime - lunch
endtime - starttime - lunch
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
angus_young_acdc,
Apologies for losing track of your question. If you're still interested in pursuing an answer, then I'll be glad to get back to work on it. If not, then I understand. If you do decide to continue work on the question, then would you mind uploading a copy of the form so I can have a look at it?
Apologies for losing track of your question. If you're still interested in pursuing an answer, then I'll be glad to get back to work on it. If not, then I understand. If you do decide to continue work on the question, then would you mind uploading a copy of the form so I can have a look at it?
I see two possibilities.
1. Use start and end date fields for lunch and calculate the time difference between them.
2. Use a numeric field and use the DateAdd function to subtract the value entered in minutes. For example
WorkingTime = DateAdd("n", -NumericFieldName, RegularTime)