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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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
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
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

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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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
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
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
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
Gustav BrockCIOCommented:
OK.
That line converts a possible day count of the value to hours.

/gustav
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.