# 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
###### Who is Participating?

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.

Analyst Assistant Commented:
Perhaps.
``````Duration(mins):DateDiff("n", TimeValue(Shift_From_Hours & ":" & Shift_From_Minutes), TimeValue(Shift_To_Hours & ":" & Shift_To_Minutes))
``````
0
Author 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)))
``````
However still receiving an error at some records.

PS, Also corrected field names as were wrong posted.

Thanks,
Ben
0
Author 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
Analyst Assistant Commented:
Ben

What values are in the fields that still cause errors?
0
Author 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
Analyst 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