You are viewing limited content. For full access, please sign in.

Question

Question

Timevalue() of 12:00 AM and 01:00 AM in the Time Picker result in 00:00 and 01:00 instead of a decimal number

Forms
Updated March 24, 2020
asked on October 2, 2019

I have a time sheet form with single line fields using the Time Picker style for Start Time and End Time, and having trouble getting the Hours (for hours worked) number field calculation to work whenever 12:00 AM or 01:00 AM are entered in Start Time or End Time. 

I tried to troubleshoot by entering the following calculation for the "Hours" number field.

=TIMEVALUE(INDEX(OT_Table.End_Time,ROW()))

The screenshot below shows what Hours gets calculated to, given what gets entered into the End Time field. I don't know why 12:00 AM and 01:00 AM result in 00:00 and 01:00, when all other times result in a decimal number.  Is this a bug? 

Time Picker.png
Time Picker.png (6.75 KB)
Download
0 0

Answer

SELECTED ANSWER
replied on October 3, 2019

Hi Mandi,

Try this calculation for getting the difference in hours between two times, rounded to 2 decimal places:

=ROUND(MULT(SUB(VALUE(INDEX(Table.End_Time, ROW())), VALUE(INDEX(Table.Start_Time, ROW()))),24),2)

My table is just named "Table", so you should modify the variable names Table.Start_Time and Table.End_Time to point to your given names. Hope this helps!

2 0
View 6 previous replies
replied on October 3, 2019

Thank you so very much! This worked perfectly!

I noticed that you used VALUE, instead of TIMEVALUE, which might make a difference, too (?).

0 0
replied on October 3, 2019

Yes - I'm multiplying the values by 24 so a decimal fraction of a day turns into an hour, and not using them as time types. That's the Mult ... ,24 part of the calculation.

As an aside, the Round... 2 on the outside is what rounds to 2 decimal points. 

1 0
replied on March 24, 2020

Hi there!  

Can you help me create the formula for a timesheet form I am creating?  I am trying to do something similar to enter a time in field and time out field and calculate the number of total hours worked and have the ability to add additional rows to calculate the time and time out for the timesheet.  I attached a picture of the fields I am trying to configure in Laserfiche forms.  Thank you!

Screen Shot 2020-03-24 at 8.56.57 AM.png
Screen Shot 2020-03-24 at 8.56.57 AM.png (98.88 KB)
Download
0 0
replied on March 24, 2020

From the screenshot it looks like you would put the formula into the "Hours Worked" field by clicking the "Field options" link on the right. In the Advanced tab, paste the formula. You may also want to set the field to read-only, unless you want users to change the result of the calculation.

You will have to change the Table.End_Time and Table.Start_Time variables to match the naming you already have. Being careful not to change the other syntax, use the > symbol to find your start time variable. Then, do that again for the end time variable.

1 0
replied on March 24, 2020

Yes!  Thank you that worked.  Now I can't figure out the Total hours' calculation portion of the form.  I would like to calculate anything that is entered in the Table/ Rows and have an over Total Hours worked for the week/ month.  This is the actual formula I used and is adding the Time In and Time Out.  

=Sub(INDEX(variable.Time_Out_5,ROW()),INDEX(variable.Time_In_5,ROW()))*24

Screen Shot 2020-03-24 at 9.12.46 AM.png
Screen Shot 2020-03-24 at 9.12.46 AM.png (86.15 KB)
Download
0 0
replied on March 24, 2020

Great! The total hours worked is pretty simple: in the advanced tab for the Total Hours Worked field, use the formula:

SUM(variable.Hours_Worked)

Note: you may have to double-check the variable name of the field "Hours Worked". For more on formulas, see Formulas in Laserfiche Forms. Also, check out the Business Process Library (click Design in the top bar in Forms, and choose the rightmost button) - it has a process called CSS/JavaScript/Formula Examples which you may find interesting.

1 0
replied on March 24, 2020

Thank you so much!  I'm sorry to keep asking on this thread but you are the only one that is answering.  One last thing, I entered a number field to enter employee numbers but our employee numbers start with 0 and are 6 digits (ex:  0001234) but the number field defaults to the numbers that are not zero.  How do I format the field to allow 0 to be entered?

Screen Shot 2020-03-24 at 3.45.15 PM.png
Screen Shot 2020-03-24 at 3.45.15 PM.png (74.46 KB)
Download
0 0
replied on March 24, 2020

Happy to help!

For a number that starts with 0s, you should actually use a Single Line Field (you can delete the field and variable off your form, and drag on the single line field instead). The reason we are using a text field is that this more of a numeric identifier (like a phone number or a social security number) than a number you can count/do calculations with.

Then, you can do the following:

In the Advanced Tab, in "regular expression for validation" enter the pattern:

\d{6}

which means any digit, exactly 6 times. (See Help File)

To help your users understand what to put in the field, you can write them a friendly tooltip (type a message in the Tooltip section). To show them a friendly error message when this happens, you can optionally configure a custom error message as shown below (also see Help File):

If you want to get even fancier, you can look into field masks (see Help File) but that might be overkill for this.

1 0
replied on March 24, 2020

Thank you so much!  I got the form to work perfectly!  Thank you for responding and assisting me today!

1 0

Replies

You are not allowed to reply in this post.
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Attachments
Related Posts
Loading ...