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 4
bfuchsAsked:
Who is Participating?
 
NorieConnect With a Mentor VBA ExpertCommented:
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
 
NorieVBA ExpertCommented:
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
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
 
NorieVBA ExpertCommented:
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
 
bfuchsAuthor Commented:
Thank you!
0
 
bfuchsAuthor Commented:
0
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.

All Courses

From novice to tech pro — start learning today.