Input Time

[Role21]

Hi,

I'm trying to create a Time Sheet Web Form. I want my users to enter their start time and end time. However, I don't see a time picker option, I only see a date picker. I don't want my users to enter an invalid time value.

How will I achieve this?

[JolliBeng]

Hi @Role21,

Yes, Caspio does not have a time picker option. What you can do is separate the input fields for your date and time fields. That will require some javaScript, you may refer to this Tech Tip for more details.

You may also check this sample Time Sheet Web Form.

 

-JolliBeng

[Role21]

Great! Thanks for the tip!

I also want to calculate the time accumulated. It is like End Time - Start Time. How will I be able to calculate that?

[JolliBeng]

Hi @Role21,

You can do that using a DateDiff function. You need to use a Calculated Field on your report and enter the following formula:

(Datediff(MINUTE,[@field:Start_Time],[@field:End_Time])) /60

 

-JolliBeng

[Role21]

This works. But why is it not working when I tried to put it on a Formula field? Calculated fields are for display purposes only. I want it to be saved on the table and download it as an excel file.

[JolliBeng]

HI @Role21,

The DateDiff formula will not work on Formula fields, because formula fields do not accept SQL subqueries. If you want to download the records as an excel file, you can do that via report DataPage.  You just need to enable the download options as Excel. It will also include the values from calculated fields.

You may check this sample Report.

 

-JolliBeng

[Jess147]

What if I want to get the total number of hours per day?

[JolliBeng]

You can use Totals  and Aggregations  to calculate the total number of hours.

https://howto.caspio.com/datapages/reports/advanced-reporting/totals-and-aggregations/

 

-JolliBeng

[Jess147]

That works. But I need to see the total per day. 

Any advise ?

[JolliBeng]

I see. You can enable the data grouping per your date field then show the totals per group.

Data Grouping:https://howto.caspio.com/datapages/reports/advanced-reporting/data-grouping/

 

-JolliBeng 

[JolliBeng]

This screenshot might also help:

[Glitch]

On 6/22/2019 at 10:54 PM, JolliBeng said:

HI @Role21,

The DateDiff formula will not work on Formula fields, because formula fields do not accept SQL subqueries. If you want to download the records as an excel file, you can do that via report DataPage.  You just need to enable the download options as Excel. It will also include the values from calculated fields.

You may check this sample Report.

 

-JolliBeng

Hi there, I think Datediff formulas does work on formula fields, except if you are using GetDate().

Datediff will work if the dates are deterministic (static). So if you're just computing for how many minutes were spent between start time and end time, DateDiff(minutes, starttime, endtime) should work in the Formula datatype. 

[Kurumi]

Hi everyone - Just wanted to share this solution. If you want to save and select Date and Time in (1) Text Field instead of separating them into different fields. You may use an external JS library called flatpickr: https://flatpickr.js.org/getting-started/ 

To know more, you can check the post here: 

 

[Kurumi]

Hi - Just to add, here's a new post to disable the dates in a custom calendar date picker: 

 

[Flowers4Algernon]

Hello, this might also be helpful: 

 

[Kurumi]

Hi - I just wanted to share this workaround for saving time data. You can use this external script: https://flatpickr.js.org/examples/

To apply this in the DataPage, insert this code in the Header:

<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/flatpickr/dist/flatpickr.min.css">

<style>

.flatpickr-calendar {

 font-family: system-ui !important;

}

</style>

Footer:

<!-- jQuery -->
  <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
 <!--  Flatpickr  -->
  <script src="https://cdn.jsdelivr.net/npm/flatpickr"></script>

<script>

$("#InsertRecordTime").flatpickr({
    enableTime: true,
    noCalendar: true,
    dateFormat: "h:i K"
});
</script>

InsertRecordTime - this should be changed with your right FIELDNAME. Take note that the Data Type of the field is Text(255) and disable the Calendar Popup.

Result:

To know more about the time, see this link: https://flatpickr.js.org/examples/#time-picker

[TheAdventure]

On 5/30/2023 at 2:30 PM, Meekeee said:

Hi - I just wanted to share this workaround for saving time data. You can use this external script: https://flatpickr.js.org/examples/

To apply this in the DataPage, insert this code in the Header:

<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/flatpickr/dist/flatpickr.min.css">

<style>

.flatpickr-calendar {

 font-family: system-ui !important;

}

</style>

Footer:

<!-- jQuery -->
  <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
 <!--  Flatpickr  -->
  <script src="https://cdn.jsdelivr.net/npm/flatpickr"></script>

<script>

$("#InsertRecordTime").flatpickr({
    enableTime: true,
    noCalendar: true,
    dateFormat: "h:i K"
});
</script>

InsertRecordTime - this should be changed with your right FIELDNAME. Take note that the Data Type of the field is Text(255) and disable the Calendar Popup.

Result:

To know more about the time, see this link: https://flatpickr.js.org/examples/#time-picker

Hi MeeKee,

This works but only on page load. The user has to refresh the webpage in order for the picker to work again. I have a form and a table with results both displayed on my web page. How do I get the javascript to  automatically refresh itself and allow submissions after the initial time submission form is sent?

Thanks!

[CoopperBackpack]

Hello @TheAdventure,

Please test the code that utilizes the DataPageReady event.

So, in the Header add the styles and CDNs:
 

<style>
.flatpickr-calendar {
    font-family: system-ui !important;
}
</style>

 <!--  Flatpickr  -->
<script src="https://cdn.jsdelivr.net/npm/flatpickr"></script>
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/flatpickr/dist/flatpickr.min.css">

In the Footer:

<script>
document.addEventListener('DataPageReady', customCalendarHandler);

function customCalendarHandler(){

const timeField = document.querySelector(('input[id*="InsertRecordTime"]')); //Time is the field name that should be replaced if needed
timeField.flatpickr({
    enableTime: true,
    noCalendar: true,
    dateFormat: "h:i K"
});

}
</script>

Hope this fixes the issue. 

[MVPofInnovation]

Thanks for sharing this information. I got it mostly working but I'm having trouble with the positioning of the picker pop-up.

The form field is in the middle of the page and the pop-up is way down to the left. It seems to be positioned relative to the Body, not the element that contains the field.

Any suggestions?

[Kurumi]

Hi, @MVPofInnovation - do you have a sample screenshot of the look/style or URL to check?

[Kurumi]

Hi! Caspio now offers integration with OpenAI, so you can use extensions to leverage AI to update your data based on a prompt. Here's a sample use case to get the time only based on the provided Date/Time field.

Request:
Get the time in "[@field:Date_Submitted]" and change it to 12-hr time.
Determine if it is AM or PM.
Return only the final result in the format of:
HH:MM AM or PM

Result:

Change the 'Date_Submitted' field to your right field name.

To learn more about Extensions, you can check it here: https://howto.caspio.com/integration/extensions/