?
Solved

Outlook Forms - Calculating Time Spent At Work?

Posted on 2008-10-31
7
Medium Priority
?
491 Views
Last Modified: 2011-01-09
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?
0
Comment
Question by:angus_young_acdc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 22849551
Hi, angus_young_acdc.

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)
0
 
LVL 15

Author Comment

by:angus_young_acdc
ID: 22850397
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
0
 
LVL 76

Expert Comment

by:David Lee
ID: 22851143
Are you using the "Calculate this formula automatically" option?  If so, then you should be able to code that in the formula box.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 15

Author Comment

by:angus_young_acdc
ID: 22865489
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).
0
 
LVL 76

Expert Comment

by:David Lee
ID: 22885124
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
0
 
LVL 15

Accepted Solution

by:
angus_young_acdc earned 0 total points
ID: 22894242
Hi BlueDevilFan

In the Display tab of Properties it is listed as txtMondayDuration (so I'm guessing a control), and in the Value tab the field is MondayDuration of type Date/Time, format 12:37, property to use is Value.

However what you have suggested is already what is there, but will not meet the requirements.  The Lunch option is not a Date/Time it needs to be more numeric so that (for example) a value of 35 minutes could be entered for lunch and that value used in the subtract.  However I seem to be having problems with the conversion.

My reasoning for using Numeric is because if I enter 0:30 for the lunch duration, the controls display will change to 12:30.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 23112113
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?
0

Featured Post

AWS Certified Solutions Architect - Associate

This course has been developed to provide you with the requisite knowledge to not only pass the AWS CSA certification exam but also gain the hands-on experience required to become a qualified AWS Solutions architect working in a real-world environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows functions GetTickCount and timeGetTime retrieve the number of milliseconds since the system was started. However, the value is stored in a DWORD, which means that it wraps around to zero every 49.7 days. This article shows how to solve t…
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question