What is the best way to convert number fields into time for calculations?

Hi Experts,

I have 4 number fields as follows

Shift_From_Hours (2 digits)
Shift_From_Minutes
Shift_To_Hours (2 digits)
Shift_To_Minutes

What is the best way to perform calculations like get the qty of minutes between the shift_from and shift_to fields?
LVL 6
bfuchsAsked:
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.

NorieAnalyst Assistant Commented:
Perhaps.
Duration(mins):DateDiff("n", TimeValue(Shift_From_Hours & ":" & Shift_From_Minutes), TimeValue(Shift_To_Hours & ":" & Shift_To_Minutes))

Open in new window

0
bfuchsAuthor Commented:
Hi,

Modified yours to include nz() as follows
Duration(mins): DateDiff("n",TimeValue(nz([Shift_From_Hour],0) & ":" & nz([Shift_From_Minute],0)),TimeValue(nz([Shift_To_Hour],0) & ":" & nz([Shift_To_Minute],0)))

Open in new window

However still receiving an error at some records.

PS, Also corrected field names as were wrong posted.

Thanks,
Ben
0
bfuchsAuthor Commented:
Realized these are due to some wrong entries, like 830 for shift_from_hour.
Is there any way to avoid it?
Will it be a big performance cost to handle those records?
If yes I would have users correct them.

Thanks,
Ben
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

NorieAnalyst Assistant Commented:
Ben

What values are in the fields that still cause errors?
0
bfuchsAuthor Commented:
830,  700 1900.
I'm ok if those are just counted as 0.
As long they don't give me an error.

Thanks,
Ben
0
NorieAnalyst Assistant Commented:
Ben

If TimeSerial was used it should avoid errors but with values like 830, 700 etc you'll end up with some rather strange, but obviously wrong, results.

Duration: DateDiff("n",TimeSerial(Nz([Shift_From_Hour]),Nz([Shift_From_Minute]),0),TimeSerial(Nz([Shift_To_Hour]),Nz([Shift_To_Minute]),0))
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
bfuchsAuthor Commented:
Thank you!
0
bfuchsAuthor Commented:
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.