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

Question

Question

ADD hours to timevalue

asked on May 28, 2020 Show version history

I have a timesheet table and I wanted to add a formula to the Final Out Time that would take the Initial Time In and add 9 hours to it.  Something like this:

=IF(INDEX(Timesheet.vTime_in,ROW())<>"",
ADD(TIMEVALUE(INDEX(Timesheet.vTime_in, ROW())),9),0)

But, of course, this just adds 9 to the Timevalue without formatting it correctly as a Time.  I'm guessing I might need to use the HOUR formula but I'm not finding good references and examples to help me out.

Just as a simple test I tried a formula that shows the Time In if there's a value there:

=IF(INDEX(Timesheet.vTime_in,ROW())<>"",
TIMEVALUE(INDEX(Timesheet.vTime_in, ROW())),0)

It kind of works, but doesn't indicate AM or PM.  Any ideas on this too?

Thanks.

timecalc.png
timecalc.png (7.74 KB)
0 0

Answer

SELECTED ANSWER
replied on May 29, 2020 Show version history

Fortunately Forms imports moment.js by default, which makes working with date and time values WAY easier than it is with vanilla JavaScript and JQuery.

I set a custom CSS selector on the table ("timeSheet") and additional custom CSS selectors on the time in and time out fields ("timeIn" and "timeOut" respectively in my example).

$(document).ready(function(){
  // trigger when time in changes on any row of table
  $('.timeSheet').on('change','.timeIn',function(){
    // get time value and am/pm value
    var t1 = $(this).find('input').val();
    var p1 = $(this).find('select').val();
    
    // convert values to moment.js time object
    var time = moment.utc(t1 + p1,'h:mm:ss a');
    
    // add 9 hours
    time.add(9, 'hours');
    
    // get new time and am/pm values
    // need to use lowercase am/pm to match actual value attributes of am/pm options
    var t2 = time.format('h:mm:ss');
    var p2 = time.format('a'); 
    
    // find time out field for current row
    var timeOut = $(this).parent('tr').find('.timeOut');
    
    // set time and am/pm values
    timeOut.find('input').val(t2).change();
    timeOut.find('select').val(p2).change();
  });
});

This code can certainly be condensed into far fewer lines of code, but I kept it in a "one step at a time" format to make it easier to understand what is being done.

Of course if you switched to 24H time or something you would need to change the formatting, but this worked for h:mm:ss am/pm like you had in your screenshot.

One thing to note however is that changes made with JavaScript won't be saved if your field is read-only when the change occurs, so if you plan to make "time out" a read only field you'll need a couple extra lines of code.

 

Here is a link to a moment.js cheatsheet that explains the formatting I used.

https://devhints.io/moment

3 0

Replies

replied on May 28, 2020 Show version history

Hi Mike,

TIMEVALUE converts it to a fraction of a day, so try adding 9/24 (0.375) to the TIMEVALUE result in your first formula.

0 0
replied on May 28, 2020

That makes sense, but I'm getting a calculation error:

 

=IF(INDEX(Timesheet.vTime_in,ROW())<>"",
ADD(TIMEVALUE(INDEX(Timesheet.vTime_in, ROW())),9/24),"")

There aren't any syntax errors after I edit the formula and refresh, but the resulting calculation generates an error.

 

0 0
replied on May 29, 2020

The division may be the issue. Try using 0.375 instead of 9/24.

0 0
replied on May 29, 2020

It doesn't like that either.  Here's the syntax again...maybe I missed something:

=IF(INDEX(Timesheet.vTime_in,ROW())<>"",
ADD(TIMEVALUE(INDEX(Timesheet.vTime_in, ROW())),0.375),"")

0 0
replied on May 29, 2020

The only thing I can think of is to check your variable names and/or add them to the formula again. I created a table with the exact same variable names (Timesheet and Timesheet.vTime_in) and pasting in your formula worked for me.

0 0
replied on May 29, 2020

I double checked the variables and they are good.  When I pick a time from the drop-down list it doesn't appear to trigger the formula in the vTime_out field.  When I type a time and tab out of the vTime_in field, then it triggers the formula and shows the error.

0 0
replied on May 29, 2020

I'm not sure what version of Forms everyone on this thread is using, but I couldn't get this to work with his formula either (I'm on 10.4.1.164).

I tried testing things out with both tables and independent fields and there seems to be some underlying issue with the way Forms is interpreting the Time variable.

If I do a formula like =ADD(0.5,0.375) or =ADD(0.5,TIME(9,0,0)) or =ADD(0.5,9/24)

It works every time.

However, as soon as I replace "0.5" with a time variable, everything falls apart and I get an empty result.

 

The only way I've been able to get this to work is with an additional "middleman" number field set equal to the first time value.

For whatever reason, converting the time to a decimal number first using this third field and referencing that instead allows the calculation to work to some degree.

 

First, I have a "Time_In" variable

Next, I have a "Time_Number" variable set equal to that time with 30 decimal places to ensure (hopefully) nothing gets cut off.

Then, in my "Time_Out" variable I add the hours to the numeric time instead of directly to the time variable and it works. 

=ADD(INDEX(Time_Table.Time_Number,ROW()),0.375)

But.... in the table it still doesn't recognize AM/PM when the time changes. Although the same approach works perfectly fine in independent fields.

 

=ADD(Time_Number,TIME(9,0,0))

 

However, if I switch the Time to 24 Hour, the table works as expected.

 

It really seems to me that there's some buggy behavior going on with the time fields, so you might just want to try JavaScript instead.

Time is a new-ish field type and it looks like there are still some wrinkles.

1 0
replied on May 29, 2020

I'm on version 10.4.3.198, so perhaps it was fixed recently. Using an intermediary field is a good fix.

0 0
replied on May 29, 2020

I'm on 10.4.1.164.  No idea how to do this in javascript so if there are some good examples you're aware of please advise.

0 0
SELECTED ANSWER
replied on May 29, 2020 Show version history

Fortunately Forms imports moment.js by default, which makes working with date and time values WAY easier than it is with vanilla JavaScript and JQuery.

I set a custom CSS selector on the table ("timeSheet") and additional custom CSS selectors on the time in and time out fields ("timeIn" and "timeOut" respectively in my example).

$(document).ready(function(){
  // trigger when time in changes on any row of table
  $('.timeSheet').on('change','.timeIn',function(){
    // get time value and am/pm value
    var t1 = $(this).find('input').val();
    var p1 = $(this).find('select').val();
    
    // convert values to moment.js time object
    var time = moment.utc(t1 + p1,'h:mm:ss a');
    
    // add 9 hours
    time.add(9, 'hours');
    
    // get new time and am/pm values
    // need to use lowercase am/pm to match actual value attributes of am/pm options
    var t2 = time.format('h:mm:ss');
    var p2 = time.format('a'); 
    
    // find time out field for current row
    var timeOut = $(this).parent('tr').find('.timeOut');
    
    // set time and am/pm values
    timeOut.find('input').val(t2).change();
    timeOut.find('select').val(p2).change();
  });
});

This code can certainly be condensed into far fewer lines of code, but I kept it in a "one step at a time" format to make it easier to understand what is being done.

Of course if you switched to 24H time or something you would need to change the formatting, but this worked for h:mm:ss am/pm like you had in your screenshot.

One thing to note however is that changes made with JavaScript won't be saved if your field is read-only when the change occurs, so if you plan to make "time out" a read only field you'll need a couple extra lines of code.

 

Here is a link to a moment.js cheatsheet that explains the formatting I used.

https://devhints.io/moment

3 0
replied on May 29, 2020

Cool...I will try it out.

0 0
replied on May 29, 2020

I updated it with my table name and column names using the variable names, but it doesn't seem to be doing anything.  I copy/pasted just the section of code that follows $(document).ready(function(){ since I have other code there for a calendar object.

// trigger when time in changes on any row of table
  $('.Timesheet').on('change','.vTime_in',function(){
    // get time value and am/pm value
    var t1 = $(this).find('input').val();
    var p1 = $(this).find('select').val();
    
    // convert values to moment.js time object
    var time = moment.utc(t1 + p1,'h:mm:ss A');
    
    // add 9 hours
    time.add(9, 'hours');
    
    // get new time and am/pm values
    var t2 = time.format('h:mm:ss');
    
    // need to use lowercase am/pm to match actual value attributes of am/pm options
    var p2 = time.format('a'); 
    
    // find time out field for current row
    var timeOut = $(this).parent('tr').find('.vFinal_out');
    
    // set time and am/pm values
    timeOut.find('input').val(t2).change();
    timeOut.find('select').val(p2).change();
  });  

 

0 0
replied on May 29, 2020 Show version history

Are you getting any errors in the browser's dev console?

It should not be the table or field variable names. With that selector it would need to be a custom CSS class set in the advanced tabs (they are also case-sensitive).

JQuery uses selectors just like CSS does, so when it sees .Timesheet it is looking for any element with a class value (denoted by the period) of "Timesheet"

For example, my table is set like this:

1 0
replied on May 29, 2020

Ok, I think I get it. I'll try again. Thanks.

0 0
replied on May 29, 2020

That worked.  Thanks!

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

Sign in to reply to this post.