Hi Guys , I need to add a numeric field value to a specific hour but i faced a #Func problem , what i need to do is add for example 3:20 hours to 8:00 AM the 3:20 is the result of the expression [DaysRemains]*24 :
Further to MajP's comment, have you looked at the DateAdd details and example? Your parameters are questionable.
It might be easier for readers to respond if you told us what you were trying to do in simple English, or provided a sample of the before and after values.
No way is days remaining * 24 giving you 3:20. At best it is giving some decimal value unlikely to represent 3 hours and 20 minutes. What is days remaining? A date time, or integer? What do you start with and what are you looking for.
People get elapsed hours and minutes with times confused. If I want 46Hours and 3 minutes that is not a time of 46:03.
Time & Date I need this "Dates" page as a general page for the top of my Dates Menu. Will add more information about dates as I find useful stuff on the internet. In the meantime, just below here is a drop down menu which gives you links to the stuff already on my website
I think we need to know what is in [DaysRemains] because your math there appears to be all wrong. More specifically, what data type is that field and what are typical values you would expect to see there?
Dates are stored internally as a DOUBLE (scientific), essentially a count of days and fractions thereof since a reference date. Hours are found in the fractions of a day. You can often do a DateAdd if you wish but it is possible to do the direct addition without a function, and in your case it might be advisable. Using the DateAdd where appropriate is still very useful as it provides mnemonic value if the code is complex, but see also my notes below about DateAdd.
To add 3 hours and 20 minutes to 8:00 AM, the expression might resemble:
Code:
NewTime = #08:00:00# + #03:20:00#
Without knowing the contents of [DaysRemains] I couldn't advise on how to do what you wanted, but I am almost dead-certain that multiplying it by 24 isn't going to be right. Divide by 24? Maybe. Multiply? Highly unlikely to be correct. But without knowing the format of your variable, we can't truly proceed.
Here is the description of DateAdd, and one part of the description that I will copy for emphasis tells me that it is unlikely that you would get away with adding exactly as you specified:
You might convert 3:20 to minutes and get a LONG value but as a non-integer number, that 3:20 would be rounded with a result that is not going to be quite what you expected. DateAdd might not be the right way to go in this case.
Also, because it is expecting a LONG as a number, DateAdd doesn't accept a specifier such as "H:N" for the units addition. You could use "H" or "N" to add hours or minutes individually, but you cannot add their combination in a single call. That, too, is described in the DateAdd writeup.
Here are two functions to add hours and minutes to a time. Understand that it is meaningless without a date. Because adding 17 hours to 8:00 AM gives you 1:00:00 AM which is really means nothing.
I am guessing here but you want to add an amount of hours to a time on a given date. Here are two functions. You can pass in a starttime or assumes 08:00, optional start date or it assumes today, and hours and minutes. To make it easy you can pass in fractional hours (3.333 = 3 hours 20 mins) or pass in hours and minutes separately in another format. In either case it returns a date and time. You can choose not to display the date or not.
Code:
Public Function AddHrsMins(fractionHours As Double, Optional StartTime As Date = #8:00:00 AM#, Optional StartDate As Date = #1/1/9999#) As Date
If StartDate = #1/1/9999# Then StartDate = Date
StartDate = Int(StartDate) + TimeValue(StartTime)
AddHrsMins = StartDate + fractionHours / 24
End Function
Public Function AddHrsMins2(theHours As Integer, theMins As Integer, Optional StartTime As Date = #8:00:00 AM#, Optional StartDate As Date = #1/1/9999#) As Date
If StartDate = #1/1/9999# Then StartDate = Date
StartDate = Int(StartDate) + TimeValue(StartTime)
AddHrsMins2 = StartDate + theHours / 24 + theMins / (24 * 60)
End Function
To test all the choices (both functions and all the options)
Code:
Public Sub Test2()
Dim fractionHrs As Double
Dim theHours As Integer
Dim theMins As Integer
fractionHrs = 3.3333 ' that is 3 hours and 20 minutes
theHours = 3
theMins = 20
Debug.Print AddHrsMins(fractionHrs)
Debug.Print AddHrsMins(fractionHrs, #10:00:00 AM#)
Debug.Print AddHrsMins(fractionHrs, #11:00:00 AM#, Date - 2)
Debug.Print AddHrsMins(fractionHrs, #10:00:00 PM#)
Debug.Print
Debug.Print AddHrsMins2(theHours, theMins)
End Sub
Results
Code:
4/25/2021 11:20:00 AM
4/25/2021 1:20:00 PM
4/23/2021 2:20:00 PM
4/26/2021 1:20:00 AM
4/25/2021 11:20:00 AM
The Date data type is a point in time. It is stored as a double precision number. The integer portion is the number of days since 12/30/1899 and the decimal is the fraction of the day since midnight. So, the time part NEVER exceeds 23:59:59. If you add 1 hour, you end up with 00:59:59 for the NEXT DAY.
So, the bottom line is you can accumulate hours in a date field but you will need to convert the stored value to something meaningful because it will display as 12/31/1899 00:59:59.
