Visokio website     Downloads     Video tutorials     KnowledgeBase  
Date/Time: Calculating and comparing durations? - Visokio Forums
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.
  • 5 Comments
  •     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")

    image
    Attachments
    DateDiff.JPG 80K
  •     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
This discussion has been closed.
← All Discussions

Welcome!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership