How can I change a text field to date/time for calculating duration-CodePudding

I am trying to calculate the duration of time from the start field to the end field from a dbo_table. I'm pulling the table from my scheduling software so I have no control over the formatting. The text formatted fields are entered as "09:00" and "15:30" to correspond to 9:00am and 3:30pm. I need to some how convert them into time so that they can be mathematically summed to achieve the duration of time. Any and all help would be greatly appreciated!! Thank you.

CodePudding user response：

Try DateDiff() function with the text data. Access should automatically cast to date/time. Example:

DateDiff("n","09:00","15:30")

returns 390 minutes.

This does assume time range is in same day.

CodePudding user response：

Use CDate to convert to Date, then subtract:

Duration = CDate(CDate([TimeEnd]) - CDate([TimeStart]))

The duration you can format as time using the format h:nn.