Negative Time. Is it possible ?

[kostaskir]

Hello everybody,


I would like to ask you something that until now I haven’t found any answer. Official Microsoft says that it can’t be done. But honestly I must find a solution asap. As my boss said.



I have an exported excel sheet from the SAP system. Then I have to import this file to an Access Database and produce a numerous reports. The title of the program is “Time Management” in case you wonder.



Now the problem is that I have to subtract fields with“time” data type in order to calculate over hours. But what happens if the value is negative ?



Example:



All the employees start 9:00 and go home at 17:00. So if the employee one day stopped working at 17:30 his over hour is 30 minutes. The next day If he stopped working at 16:20 for some reason then I have to subtract 30mins-40mins, and the final over hour is -10 minutes.



So annual Over Hours = Over Hours – (Time that has left earlier).
So the questions is what I have to do if a have an employee with negative time ? In excel you can’t have negative time. But I need a number to do the calculations.

Thank you so much. Your help would be highly appreciated.

 

[Beetle]

You could use the DateDiff function. In the following example it would return -10;

DateDiff("n",#5/2/2011 8:25 AM#,#5/2/2011 8:15 AM#)

from the immediate window;

?DateDiff("n",#5/2/2011 8:25 AM#,#5/2/2011 8:15 AM#)
-10

 

[jzwp22]

Access actually stores date/time values as a number with the integer portion indicating the number of days since 12/31/1899 and the decimal portion indicating the fraction of a day. So in effect when you subtract one time from another you are actually subtracting two numbers, as such you technically can have a negative number depending on which is greater. I would recommend that you use the datediff() function within Access to do your subtraction.

All the employees start 9:00 and go home at 17:00. So if the employee one day stopped working at 17:30 his over hour is 30 minutes. The next day If he stopped working at 16:20 for some reason then I have to subtract 30mins-40mins, and the final over hour is -10 minutes.

Basically the employee works 510 minutes one day and 440 minutes another day, but if everyone is suppose to work from 9-5 that would be 480 minutes/day or 960 minutes over the two days. The employee in question worked a total of 950 minutes over the two days, so in effect they worked 10 less minutes than they should have, but they still worked both days. You should not take everything in reference to 17:00 because what would happen if someone comes in before 9AM? You should be comparing the total time worked per day to the total time a person must work/day. This shortage in time worked could be viewed as a negative number but it is not a negative time just a negative number.

I don't know if your pay rates are different for time worked in excess of the 480 minutes/day, but that would be up to your business rules.

 

[The_Doc_Man]

Track all times as Date fields, but then when actually working with them, come up with a formatting routine. In summary, build a string function to give you a time string based on a number of days and fractions thereof, which is the REAL way time is stored.

When you want to compute elapsed time, truncate the variable to a number using FIX command to split days from fractional days. Then use Format$(fracs, "hhh:nn:ss" ) to get fractional dates in a decent format. If the input number happens to be negative, you can remember that you needed a minus sign before you convert the string. Check for minus, convert the number to positive, and then format it.

 

[kostaskir]

I took in serious consideration your advices. It is more clear how I have to design the project now.

Thank you for the reply.

 

[gemma-the-husky]

i would forget dealing with these as date-time records. another problem you will have is that the time bit (in a vba sense) is only a fraction of a day, and cannot exceed 24hrs.

so, if you used datetime data types, then a worker working five days of 8hrs, will actually show as working 16hrs (since 40hrs is net 16hrs)

It's not quite that - but using datetime types to manage cumulative times is possibly not the best idea.

I think I would probably look to store the working times as total minutes, and divide by 60 to get hours.