Time Sheet

[ababcock]

I have multiple submission forms that need to calculate total hours based on the begin time and end time entered. The script I have entered is:

 

<script>

document.getElementById('Submit').onmouseover = function(){

 

var date = document.getElementById('InsertRecordDate_of_Service').value;

if(date == "")

date = "1/1/2011";

var hour = document.getElementById('cbParamVirtual2').value;

if(hour =="")

hour="00";

var minute = document.getElementById('cbParamVirtual3').value;

if(minute == "")

minute = "00";

var ampm = document.getElementById('cbParamVirtual4').value;

 

document.getElementById('InsertRecordStart_Time').value = date + " " + hour + ":" + minute + " " + ampm;

 

 

var hour2 = document.getElementById('cbParamVirtual6').value;

 

if(hour2 =="")

 

hour2="00";

 

var minute2 = document.getElementById('cbParamVirtual8').value;

 

if(minute2 == "")

 

minute2 = "00";

 

var ampm2 = document.getElementById('cbParamVirtual7').value;

 

document.getElementById('InsertRecordEnd_Time').value = date + " " + hour2 + ":" + minute2 + " " + ampm2;

var start= new Date(document.getElementById('InsertRecordStart_Time').value);

var end = new Date(document.getElementById('InsertRecordEnd_Time').value);

var timeDiff = end - start;

var hh = Math.floor(timeDiff / 1000 / 60 / 60);

 

timeDiff -= hh * 1000 * 60 * 60;

 

var mm = Math.floor(timeDiff / 1000 / 60);

 

var totalmin= (mm+ (hh * 60)) ;

document.getElementById('InsertRecordTotal_Units').value =  totalmin.toFixed(2);

 

};

 

 

</script>

 

This is working some of the time, but not always, and I don't know what the problem is. I just submitted a record for 6/9/15 11:00 AM to 6/9/15 2:00 PM, and it came up with 185.00 hours.

 

Does anyone know a way to make this work?

[Jan]

Hi ababcock,

 

If I understand correctly, totalmin - is the difference in minutes.

 

You can change the line with totalmin as follows:

var totalmin= (parseFloat(mm/60)+ hh);

Does it work for you?

[ababcock]

Thanks Jan! I did get it fixed to calculate in hours instead of minutes. I have another question now. For our time sheets, staff will be entering a start time and end time, so I will need the form to calculate the total hours for that time period, but there will be multiple lines/times to calculate. For an example:

 

Date          Start Time          End Time          Total

6/24/15      1:00 PM             2:00 PM             1.00

6/25/15      3:00 PM             7:30 PM             4.50

6/26/15      8:15 AM             5:00 PM             8.75

 

Total Hours 14.25

 

I can get it to calculate the first total, but when I duplicate the script for the second line, it only calculates the second line, not both. Any ideas?

[Jan]

Hi ababcock,

 

You can add a Total line to the Report. Does it work for you?

[ababcock]

I think that will work in the search and report datapage. I was working with a submission form, but I think it will be OK to view the totals on the report page. Thanks, Jan!

[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 total worked hours based on Time In and Time Out fields.

Request:
Calculate the total working hours for the following time entries:

Time In: [@field:Clock_In]
Time Out: [@field:Clock_out]

The format should be HH:MM

Insert 'File a leave' if there are no values in:

Time In: [@field:Clock_In]
Time Out: [@field:Clock_out]

Return only the final result.

Result:

Change the 'Clock_In' and 'Clock_out' fields to your right field names.

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