Subtract integer from time duration (1 Viewer)

[WineSnob]

i am trying to subtract Personal time (number) from the Total Time (time duration formatted hh:mm) to get Net Time.
I cannot seem to figure out the correct syntax. Not really sure why I am getting 15:00 either.
Looking for line 1 to be 9:00 - 3 = 6.00.
It is possible there could be a fraction like 9:00 - 3.5 = 5.5 at some point.
Thanks for any assistance.
Rick.

 

[theDBguy]

Is [Total time] column a String or a Number?

 

[GaP42]

Are you using the dateadd function? DateAdd function (Visual Basic for Applications) | Microsoft Learn
Use "h" for interval and -number of hours). If you need to deal with minutes then you need to convert and manipulate in minutes.
The time column is assumed to be a date/time data type, calculated using datediff? As such access stores that data as a date-time - so simple subtraction is inappropriate.

 

[WineSnob]

Is [Total time] column a String or a Number?

Total Time is formatted as "short time"

 

[WineSnob]

Are you using the dateadd function? DateAdd function (Visual Basic for Applications) | Microsoft Learn
Use "h" for interval and -number of hours). If you need to deal with minutes then you need to convert and manipulate in minutes.
The time column is assumed to be a date/time data type, calculated using datediff? As such access stores that data as a date-time - so simple subtraction is inappropriate.

I will look into this but I am not great with VB code.

 

[theDBguy]

Total Time is formatted as "short time"
View attachment 113721

Hi. Format and Data Type are two different things. Can you tell us the data type for that column? Thanks.

 

[WineSnob]

Total Time is a time data type

 

[theDBguy]

Total Time is a time data type

Okay, thanks. In that case, it seems the root of your problem is confusing a point in time with time duration. In other words, 9:00 means 9AM as a date/time value. When you subtract the number 3 from it, you are actually subtracting 3 days from 9AM. The answer you're getting: 15:00 actually means 3PM. So, if you're trying to calculate time duration, you'll need to use numbers and not date/time values.

Hope that makes sense...

 

[WineSnob]

Yes DB Guy that makes sense.
what would be the best way to convert start and stop time to numbers?

 

[theDBguy]

Yes DB Guy that makes sense.
what would be the best way to convert start and stop time to numbers?

You may have misunderstood. The Start and End Times should stay Date/Time fields. I was talking about the [Total Time] field, it should be a Number.

However, I just realized that Total Time came from the time difference between Start and End Time. If so, that must have been calculated somehow. If so, then it shouldn't be a field in a table at all. The difference between two date/time fields should result in a number already, so no need to convert it to a number again.

You might consider posting a sample db with test data to clarify what's going on in your db.

 

[ebs17]

DateTime values are internally numbers (double). The integers are days (since 1899-12-30), the decimal parts are fractions of a day.
So an hour is 1/24, a minute is 1/(24*60), a second is 1/(24*60*60).

? #6:00:00# * 1, 6 / 24
 0,25          0,25

Subtraction of 3 hours using a decimal component or a function.

? #9:00:00# - 3/24, DateAdd("h", -3, #9:00:00#)
06:00:00      06:00:00

Subtraction of 3.5 hours

? #9:00:00# - 3.5/24, #9:00:00# - (3.5*60)/(24*60), DateAdd("n", -210, #9:00:00#)
05:30:00      05:30:00      05:30:00

 

[gemma-the-husky]

A time part of a day is actually a fraction. So 9:00am is actually 3/8 of a day or 0.375

 

[DickyP]

Assuming what you are trying to achieve from the original two date/time values is a total number of hours (and minutes?) worked then you need to use something that doesn't return a date/time value, but a number.

To get the total time from the two date/time values use the DateDiff function using the "n" parameter to return the number of minutes and then subsequently divide by 60 to get hours and minutes.

To get just the Hours use integer division by 60 on the minutes result from DateDiff - the '\' operator , and for the minutes the modulo operator . Eg, minutes result from DateDiff MOD 60.

To get just the hours use the "h" parameter with DateDiff.

Always depend on the granularity to which you wish to store the hours. If it's just hours the then the Total, Personal and net times can be stored a Integer or Long data types, or if its for totals with fractional hours either store the minutes as long etc and manipulate later, or store as doubles having divided by 60 to get the fractional part which is the minutes.

 

[WineSnob]

Thanks to all for help.
Since I have to export the query in excel for the user anyway I will format and calculate the net time there.
I do appreciate the help though.

 

[Gasman]

I will look into this but I am not great with VB code.

Hardly difficult?
You could use DateDiff() to get the difference in start and end times in minutes.
Multiply your personal by 60 to get that as minutes, then subtract one away from the other.

Then you have the result in minutes. Convert back to hours and minutes if need be.
I used to leave it as hours decimal 9.2, 9.4, 9.25. Generally units of 6 minutes unless dead on an interval of 15 minutes.

 

[The_Doc_Man]

Since I have to export the query in excel for the user anyway I will format and calculate the net time there.

Be aware that time computations in Excel are nearly identical with time computations in Access. There is a time/date value in a cell and if you want to work in any other units than days, you have to manipulate something. It is pretty much uniform across all of MS Office that time works that way.

If you want to "diddle" with hours, minutes, and seconds, you will be doing your own formatting or encoding, because the native time unit in Office is days and fractions thereof. That is also why time intervals are such a pain... because the built-in Office time formatting code thinks you are working with days. (And in its most raw form, you ARE.)

 

[DickyP]

I endorse The Doc Mans warning. It's all down to the fact that Date/Times are a count of elapsed time in days and fractional parts of days since January 1 of the year 0001 and not simple values. If you want to to do maths you need to do it with numeric values (why DateDiff returns a Long).

I remember finding a class to do this years back and I'll have a look for it but it won't be soon. It allowed you to do things like add together 27 hours, 3 minutes and 40 seconds to 29 hours hours, 10 hours etc and get a answer in days months etc.

Don't hold your breath!

 

[gemma-the-husky]

Your personal time of 3 will be treated as 3 days. If you want 3 hours then you need 3/24, as a real number.

Note also that adding or subtracting time will often give a curious answer, as you will get the time value for a day plus time result.

So 14:00 - 10:00 will give you 4:00 (as a real number), but 10:00 - 14:00 will not give you -4:00. I think it will ignore the floating day and give you 20:00 (not tested)

14:00 + 13:00 will give you 3:00 (plus the extra day if you format for it)

 

[The_Doc_Man]

Date/Times are a count of elapsed time in days and fractional parts of days since January 1 of the year 0001 and not simple values.

Just a VERY minor correction. Access and Excel use different date reference points that are one day apart from each other. (Of COURSE Microsoft didn't keep it uniform...) And their "Day 1" dates are not in the same century. Excel references Jan 1, 1900 while Access references Dec 31, 1899 as their day 1. If I remember correctly, Windows itself agrees with Excel. The Access date differs because Microsoft obtained Access by swallowing another company and leaving their date reference intact for their product.

Otherwise, your statement is correct. Times are inherently "elapsed days and fractions since reference point."

The date confusion could be worse. Early UNIX used a 1970 reference date but OpenVMS used Nov 17, 1858. Go figure. (Actually, it's an astronomical reference point.) Of course, OpenVMS also uses 64-bit integers for its date/time reference, precise to the jiffy (=0.1 usec), so their date standard will last until the year 31068 or something like that.

 

[DickyP]

It seems to be worse than that as the whole process of dates is full of anomalies, which I don't claim to understand.

It all changed since 64 bit office such that the official stance of Microsoft is:

"Date variables are stored as IEEE 64-bit (8-byte) floating-point numbers that represent dates ranging from 1 January 100 (-657,434), to 31 December 9999 (2,958,465), and times from 0:00:00 to 23:59:59." See Date Variables on MS

If you use CDate(-657434) in both Excel & Access you get 01/01/100. Similarly if you use Clng(Date()) in both you get 45402 for today 20th April 2024. If you change a stored date in a table or a worksheet you get the same. The only time you don't get the same answer is CDate(1) or Clng(#1/Jan/1900#): this implies that all the counting in one includes day 1 and in the other excludes day 1.

Real problem is that there is a lot of documentation out there that still reflects pre-Office 2013.