Date/Time: Calculating and comparing durations?

chrisamott · August 5, 2014 11:27AM

I have an Access import data file which contains three fields. A start date/time an end date/time and a Time Period. The Time Period is the duration between the start and the end. My task is to confirm the Time Period is correct.

I have used the DateDiff function to calculate the difference between Start and End but it produces a decimal number. I have tried many different formulae and reformatting measures to compare the Time Period with my calcualtion but they all fail.

Either Omniscope cannot parse the decimal or it doesn't recognise the time to convert it to text. I can easily paste the Time Period into Excel and reformat as a general number and it produces a decimal that compares favourably with my DateDiff but I need to do this in Omniscope.

tjbate · August 5, 2014 11:33AM

Chris - What units is your imported duration value [Time Period]
in?

daniel · August 5, 2014 12:22PM

The default for the formula is to "day" so it might be decimals in terms of that.

Tried using "hour" or "minute"?

Alternatively if it is that the formula isn't reading the dates properly put in the formula:

datediff(
datevalue([Start Date/Time]),
datevalue([End Date/Time]),
"hour"
)

You shouldn't need to use the datevalue() if Omniscope is pulling in and converting those values into time (Check the Field manager on those columns).

paola · August 5, 2014 12:28PM

Formula will work on seconds/minutes/hours etc. please see the screenshot.
DATEDIFF([Value], [Value (2)], "seconds")

chrisamott · August 6, 2014 9:28AM

The format of the Time Period to which I am comparing my DateDiff is HH:mm:ss

chrisamott · August 6, 2014 10:02AM

Maybe you can find a slicker way but I took your advice and produced the DateDiff in seconds. I then converted the Time Period which was formatted in hh:mm:ss and using DateUnit converted that into seconds. I now have two fields of the same format which can be compated. Thanks

Welcome!

Categories

Ideas Parade

Tagged