How can I calculate a date/time field?

I'd like to be able to sort entries by the entered date/time, which are separate fields on my form.

I tried adding them together with the Form Calc widget but the result was a large integer, not a date.time serial number. Is there a way to get them combined correctly?

Alternatively, if I could get an Advanced Formula column that would be added to each submission, that would work but I don't see a way to do that either.

Suggestions?

Thanks - Al

To combine your separate date and time fields correctly for sorting, you can use Jotform's Advanced Formula functions in Tables, which handle date and time calculations more precisely than the Form Calculation widget. I suggest checking out the detailed guide on using Advanced Formulas in Jotform Tables to learn how to merge and format these fields properly. You can find this helpful information by using the Show Guide button below.
Show Guide

Is there a way to get the Advanced Formula appended to each submission? It looks like that's an after-the-fact data manipulation.

Hi nscrefereeforms,

Advanced Formula columns in Jotform Tables are for after-the-fact data manipulation and cannot be automatically appended to each submission as part of the form data. They allow you to create calculations and expressions that update dynamically within the table but do not alter the original submission data.

Let us know if you need further clarification.

So, it's back to the Form Calc widget if I want it in every submission.

Suggestions?

Hi nscrefereeforms,

Yes, if you want the combined date/time value in every submission, using the Form Calculation widget with conditional logic is the way to go. You can set up an Update/Calculate Field condition to combine the date and time fields into one field that gets saved with each submission.

This method ensures the combined value is part of the submission data, unlike Advanced Formulas which only manipulate data after submission in Tables.

Let me know if you want guidance on setting up this conditional logic.

Yes, please guide me through that logic.

Hi nscrefereeforms,

Here's how to set up the Update/Calculate Field condition to combine your date and time fields:

1. Go to Settings in the Form Builder.
2. Select Conditions from the left menu.
3. Add a new Update/Calculate Field condition.
4. Set the condition to calculate a field's value by combining your date and time fields (e.g., use the formula: DateField + TimeField).
5. Choose the target field where the combined result will be saved.

This will save the combined date/time value with each submission.

If you want, I can provide more detailed steps or examples.

Yes, provide detailed steps.

Hi nscrefereeforms,

Here are detailed steps to set up the Update/Calculate Field condition:

1. Open your form in the Form Builder.
2. Go to Settings and select Conditions.
3. Click "Add a new condition" and choose Update / Calculate Field.
4. Set the condition to always calculate the combined field by adding your date and time fields (e.g., DateField + TimeField).
5. Select the target field where the combined date/time will be stored (create a new field if needed).
6. Save the condition and test your form to ensure the combined value appears in each submission.

This will store the combined date/time in every submission for sorting or other uses.

The time portion of the combined serial number is incorrect. I'm getting decimals like .83 for 7AM and no decimal value for 11AM.

Hi nscrefereeforms,

The decimals like .83 for 7AM and no decimal for 11AM happen because the Form Calculation widget treats time as a fraction of a 24-hour day, but the conversion may not match expected decimal values directly.

To fix this, ensure your time field is properly formatted and consider using functions like hours() or minutes() in calculations to convert time into consistent values before adding to the date.

If you want, I can help you with a specific formula to get accurate combined date/time values.

Yes, please give me a specific formula.

Hi nscrefereeforms,

Try this formula in your Update/Calculate Field condition to get an accurate combined date/time:

DateField + (hours(TimeField) / 24) + (minutes(TimeField) / 1440)

This converts the time into a fraction of the day correctly by adding hours divided by 24 and minutes divided by 1440 to the date.

Test this and see if it fixes the decimal issue.

Hi nscrefereeforms,

Thanks for reaching out to Jotform Support. To combine the value from the date and time fields, we'll need to add the fields as String values in the formula. Let's walk through how to do it:

1. In the Form Builder, click on the Add Element button.
2. In the Form Elements window, go to Widgets.
3. Locate the Form Calculation widget in this list, then drag and drop that element into your form.
4. Add the date and time field in the formula with a space in between.
5. Convert their values from Number (0-9, black) to. String (A-Z, gray) and click Update.

Here's the result:

As for the Advanced Formula function in Jotform Table, we'll also get the same result. Let's do it:

1. In Jotform Table, click the Add button.
2. In the menu, go to Formula.
3. Select Advanced Formula from the options and click Next.
4. Add the column name.
5. Select the date and time fields to add them to the formula
6. Click Create Column.

Let us know if there’s anything else I can help you with.

That's what I was missing... the text vs number toggle on the date & time fields.

Thanks for the reply!