concatenate a date and a time field to one date/time field

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered

I have a start event and stop event that is recorded as separate date and time of day fields. I want to be able to calculate duration but need the date as some events start today but end tomorrow.

I have separate fields to assist in data entry because you can simply type "t" for today in the date field (lovely if there is a shortcut key such as "n" (now) for time - is there?) and then just type the time. In the date/time type field the shortcut "t" doesn't work so you end up having to type MM/DD/YY and HH:MM (and PM if not the default AM).

If you know of a better faster way - let me know!

Photo of Anne

Anne

  • 100 Points 100 badge 2x thumb

Posted 5 years ago

  • 0
  • 1
Photo of dwhawe

dwhawe, Champion

  • 804 Points 500 badge 2x thumb
In your formula field for duration (this is untested) you should be able to use:

ToTimestamp([end date],[end time])-ToTimestamp([beginning date],[beginning time])
Photo of Anne

Anne

  • 100 Points 100 badge 2x thumb
works perfectly - thank you
Photo of Carol Short

Carol Short

  • 184 Points 100 badge 2x thumb
I would like to create an auto numbering system on a form which would add my OEM lookup field with the current year and record number to create a unique numbering system.  Any suggestions?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,760 Points 50k badge 2x thumb
List("-", ToText(Year([Date Created]),ToText([Record ID#]))
Photo of Carol Short

Carol Short

  • 184 Points 100 badge 2x thumb
I received the following error 

Please check the syntax of your formula. Look for mismatched parentheses, missing quotes, or extra brackets.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,760 Points 50k badge 2x thumb
Try this

List("-", ToText(Year([Date Created])),ToText([Record ID#]))