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. 

[Meekeee]

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: 

 

[Meekeee]

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: 

 

[Meekeee]

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