Forum Discussion

edcooper's avatar
edcooper
Qrew Assistant Captain
4 years ago

Military time conversion

Trying to find a way to solve this:

I am doing time and date tracking for a support app.

I have a date field.
I also have a start time and end time fields where I would like to user military time:

Date:_05/12/2021____
Start Time:__1000
End Time: ____1130
I would like to convert the military times in the Start Time and End Time fields and subtract the Start Time from the End Time so

the value in Total time needs to be in hours and minutes
Total Time: __1.5 hours

Help!!



------------------------------
ed cooper
------------------------------
Formulas and functions
seattle
  • NathanUnderwood's avatar
    NathanUnderwood
    Quickbase Staff
    4 years ago

    Hi Ed,

    If I'm following, I think you just need to add a Formula - Duration field to your table. In your new Formula - Duration field, you'd want to use this formula:

    [End Time]-[Start Time]

    In the field properties of the duration field, set the Value display to HH:MM

    That should give you a Total Time in hours and minutes. 

    Let me know how that works!

    Thanks,

    Nate



    ------------------------------
    Nathan Underwood
    ------------------------------
    • edcooper's avatar
      edcooper
      Qrew Assistant Captain
      4 years ago
      Nathan,

      I don't want my users to have to enter both the date and time in StartDate and EndDate.
      I want the form to look like this:
      Date: 05/12/2021 (standard date field)
      Start Time: 1000(numeric)
      End Time: 1130 (numeric)
      Above fields entered as military time not 12 hour time
      Duration: 1.5 (formula - End Time minus Start Time)

      I'm using military time. This is my test screen for the last 3 fields.
      Start is a number representing military time.
      End is a number representing military time.
      So, if you subtract Start (1000) from End (1100) you get the value 100
      I need Tot (total time expressed in 12 hour time) which would be 1.
      If Start was 1000 and End was 1130 the Tot should be 1.5


      ------------------------------
      ed cooper
      ------------------------------
      • NathanUnderwood's avatar
        NathanUnderwood
        Quickbase Staff
        4 years ago
        Hi Ed,

        I'd recommend using Time of Day type fields for your Start/End fields. In the field properties you can set them to 24-hour clock for military time. You can then use a Formula - Duration field as your Tot field, and set the field property to HH:MM. 

        If you need to stick with numeric type fields for Start/End, you'd need to get a bit creative with your numeric Tot formula. 

        ------------------------------
        Nathan Underwood
        ------------------------------