How to use date/time field to calculate hours? / Access

In an Access form, employees need to enter the number of hours they spent for specific activities.
In the table, a “Date/time” field is used. No format or input is specified in the table.
In the form the format is “short time” and the input mask is 00:00;0;_
When the value is 00:00 in the form, it is 12:00 in the table.
How can I have 00:00 as value in the table?   Then I will be able to calculate the total of hours.
Karl001Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

yo_beeDirector of Information TechnologyCommented:
I would use number format rather than date/time since you are looking for total hours.

https://support.office.com/en-au/article/Format-Property-Number-and-Currency-Data-Types-ca77795e-b40d-4abb-b42f-daa0bb709620
0
Jeffrey CoachmanMIS LiasonCommented:
Something to clear up first:
Your input mask and format presume the "Time", ...not the number of Hours.
   ...for example: 06:00 is 6AM, ...NOT 6 Hours
....00:00 is 12 Midnight, ...NOT 0 (zero) hours.

So you have to make SURE that your users know the difference.
...and that they know that they are entering a "Duration" , ..not a specific "Time".

The good news is that you can still use that format/input mask to enter hours and minutes

Attached is a very basic sample, with totals calculated in a report
Report
JeffCoachman
Database891.mdb
0
Gustav BrockCIOCommented:
> When the value is 00:00 in the form, it is 12:00 in the table.

Normally, it is not. It will also be 00:00. Anyway, it doesn't matter which format you use when you view the table, the actual value is numeric and has nothing to do with the chosen format.

To sum the hours, just sum the date/time values.
If the hours may total beyond 24 hours, use a function like this to have a proper format for the result:
Public Function FormatHourMinute( _
  ByVal datTime As Date, _
  Optional ByVal strSeparator As String = ":") _
  As String
  
' Returns count of days, hours and minutes of datTime
' converted to hours and minutes as a formatted string
' with an optional choice of time separator.
'
' Example:
'   datTime: #10:03# + #20:01#
'   returns: 30:04
'
' 2005-02-05. Cactus Data ApS, CPH.

  Dim strHour       As String
  Dim strMinute     As String
  Dim strHourMinute As String
  
  strHour = CStr(Fix(datTime) * 24 + Hour(datTime))
  ' Add leading zero to minute count when needed.
  strMinute = Right("0" & CStr(Minute(datTime)), 2)
  strHourMinute = strHour & strSeparator & strMinute
  
  FormatHourMinute = strHourMinute
  
End Function

Open in new window

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Karl001Author Commented:
Hi Jeff,

When the total is more than 24 hours it’s doesn’t work.

testtime.jpg

Hi Gustav,
It’s working except CStr(Fix(datTime)
What the purpose of: CStr(Fix(datTime)
The value of that formula is always the same: 12:00:00 A

If I use Hour() and Minute() functions, it’s working.
testtime.jpg
0
Gustav BrockCIOCommented:
It does work, but I don't know what you do:

t = #6:16# + #3:45# + #5:00# + #10:00#
TotalTime = FormatHourMinute(t)

TotalTime -> 25:01

Fix removes a time part.

/gustav
0
Jeffrey CoachmanMIS LiasonCommented:
When the total is more than 24 hours it’s doesn’t work.
That's the downside...
:-(

Then go with what Gustav posted
0
Karl001Author Commented:
Hi Gustav,

Sorry, my comment was not to clear.
If I use your function as it is, it’s working.
If I remove “CStr(Fix(datTime) * 24” in the strHour formula , it’s working too.
So I would like to understand  the purpose  “CStr(Fix(datTime)” .

Thanks
Carol
0
Gustav BrockCIOCommented:
OK.
That line converts a possible day count of the value to hours.

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.