Negative Times

Question

It is said that any time duration can be stored in a column of DateTime data type, because the data type is implemented as the duration of time in days since 30 December 1899 00:00:00.

Assume that PaidLeave=”0:00”, SickLeave="22:00" and AvailablePaidLeave = "16:00".

When I use the following formulas, I will encounter a misrepresentation of AvailablePaidLeave at the end:

PaidLeave= PaidLeave+ SickLeave

AvailablePaidLeave = AvailablePaidLeave - PaidLeave

New AvailablePaidLeave will be "6:00" not "-6:00".

Is "-6:00" stored in the table? How can I display negative times in a form? Any written codes available for it?

Answer 1

You can add the minus sign by concatenating it to the return value of an expression if the duration is less than zero.  Using the TimeElapsed  function from my TimeArithmetic demo, to which I referred you in an earlier thread, if we first assign a negative time value of 30 hours (1.25 as a DateTime value) to a variable:

Duration = Now() - (Now()+ 1.25)

We can then return a formatted time in hours:minutes by concatenating the minus sign to the absolute value of the duration if the duration is less than zero:

? IIf(Duration <0,"-",Null) & TimeElapsed(Abs(Duration),"nn")

-30:00

Answer 2

Dear Zee,

Thank you for your quick response. Your method seems working, however, I use VBA codes instead of Queries. Additionally, I cannot change the format of a datetime to Short Time for some reason.

I can use

AvailablePaidLeave = AvailablePaidLeave - DateDif("hh:nn", SickLeave, PaidLeave)

instead of

PaidLeave= PaidLeave+ SickLeave

AvailablePaidLeave = AvailablePaidLeave - PaidLeave

I still see that "6:00" is stored in the underlying table and not "-6:00".

Do you have any solution considering my explanation above?

Percy

Answer 3

Dear Percy,

Thank you for posting in Microsoft Community. We are happy to help you.

I understand that you want to show negative sign in Access when combining two date and time columns. I did the testing on my side and this can be done using the DateDiff() function on Query Design. Kindly check the steps below on how to do it.

Setting the columns to show time only.

1. In your created table, kindly set the column properties.
2. Click on Home tab > View > Design View
3. Select the column and change the property format to Short Time, Default Value to Now(), Show Date Picker to Never.
4. Save the table and close it.

If you already set this please skip the steps above.

Applying DateDiff formula.

1. Open the table > click on Create Tab > Query Design.
2. It will open a Query tab. On the right side > double click on the table you want to create a query.
3. Click on * to show all the columns > right click on the column you want to apply the DateDiff() function > click on Zoom.
4. You can use the DateDiff() function. AvailablePL: DateDiff("h",[SickLeave],[PaidLeave]).
5. Click on OK.
6. Upper left click on Run > under Query Design tab.
7. The negative sign will show as SickLeave is the first in the formula which have bigger value than PaidLeave.
8. Save the query.

Negative will be stored in Access and can be displayed by using DateDiff function.

Related article: DateDiff Function

Hope the suggestion can help you.

We look forward to your response. Thanks for your cooperation. Stay safe and healthy always.

Sincerely,

Zee | Microsoft Community Moderator

***Note: In the event that you're unable to reply to this thread, please ensure that your Email address is verified in the Community Website by clicking on Your Account Name > "My Profile" > "Edit Profile" > Add your Email Address > tick "Receive email notifications" checkbox > click on "Save".***

Answer 4

It is said that any time duration can be stored in a column of DateTime data type, because the data type is implemented as the duration of time in days since 30 December 1899 00:00:00.

Hi Percy,

After some experimenting you can see that the Date type can handle negative (elapsed) days, but the remainder (the time part) is also taken negative.

31-dec-1899 06:00:00 gives 1.25

30-dec-1899 06:00:00 gives 0.25

29-dec-1899 06:00:00 gives -1.25 instead of -0.75

Of course, you can wirte code to handle this.

Isn't it far, far much easier to store the time as Single or Double, and use 2 simple functions, the first to convert a time-text to numerical, and the second to convert numerical to time-text?

Imb.