Timesheet to display worked hours/minutes as hours/decimal and use of military time

[CJ54235]

I am searching for some help with calculations for a timesheet that I have to convert over from Adobe LiveCycle. I am a novice in coding and have been reading through all the posts I can find as well as any referenced help articles people have pointed to for the past month. The original timesheet used FormCalc IsoTime2Num to allow for military time input (to avoid having employee input AM/PM) without needing the colon typed in. The Total Hours field displayed the time worked for the day as numeric number allowing 2 decimals.

I have managed to put together scripts that calculate the correct total hours worked for the day and display 2 decimal places for Totals. I cannot figure out how to have, for example, the Totals for Monday display as numeric (7.50) rather than 7.30. I believe it is the document level function timeConvert that needs tweaking. Maybe?

I also looking for help to convert the military time input to display as a 12-hr clock (i.e. 13:00 would display at 1:00) and user does not need to input the colon (:).

I have attached a working file (note only Monday and Tuesday rows are scripted until I figure out the correct coding) and below is what is what I have going:

***Document Level***

function timeConvert(f,f1) {
var start = f.split(":");
var finish = f1.split(":");

var a = (Number(start[0])*60)+Number(start[1]);
var b = (Number(finish[0])*60)+Number(finish[1]);
var num = (b-a);
var hours = (num / 60);
var rhours = Math.floor(hours);
var minutes = (hours - rhours) * 60 ;
var rminutes = Math.round(minutes);
if(f&&f1)
return rhours + "." + rminutes;
else
return "";}

***Morning and Afternon cells are labeled as InMon, OutMon, InMon_2, OutMon_2, InTue .... etc. ***

each cell has Format of Time as HH:MM

***Monday Totals field***

CALCULATE TAB - Custom:

//morning time calculated
var timestarted = this.getField("InMon").value;
var timefinished = this.getField("OutMon").value;

var output = timeConvert(timestarted,timefinished);
event.value = output;

//afternoon time calculated
var timestarted2 = this.getField("InMon_2").value;
var timefinished2 = this.getField("OutMon_2").value;

var output = timeConvert(timestarted2,timefinished2);
event.value = Number(event.value) + Number(output);

FORMAT TAB - Custom:

event.value = (event.value != 0)?util.printf("%,0.2f",event.value):"

Thanks in advance for any guidance!

CJ

 https://tinyurl.com/4y3numve 

[Thom Parker · Accepted solution]

First, here is a correction to the time difference script. There was an error in the "b" calculation and I've changed the output to be a number, rather than a string, which seems in line with how you want to use it. 

function DecimalTimeDiff(f,f1) {
    var nHours = 0;
    var rgTime =  /^\s*(\d{1,2})\:(\d{2})\s*$/;
    var aStart = rgTime.exec(f);
    var aFinish = rgTime.exec(f1);
    if(aStart && aFinish)
    {
       var a = (Number(aStart[1])*60)+Number(aStart[2]);
       var b = (Number(aFinish[1])*60)+Number(aFinish[2]);
       var num = (b-a);
       nHours = (num / 60);
    }
    return nHours;
}

The way to convert numbers in place is to use a custom validation script:

var rgAltTime =  /^\s*(\d{1,2})\:?(\d{2})\s*$/;
var aMatches = rgAltTime.exec(event.value);
if(aMatches && (aMatches[0] < 24) && (aMatches[1] < 60) )
    event.value = aMatches[1] + ":" + aMatches[2];
else
    event.value = "";

Note that the "if" statement includes logic for validating the entered time value.

[CJ54235]

Also to note, I do not need to worry about shift starting one day and ending another, nor daylight savings or time zone change.

[Thom Parker]

So you need to convert the #of minutes to a fraction of an hour. 

But if you want that, then there is no need for the last part of the calculation:

Here's an update, I've included some format checking. And changed the function name to better represent it's behavior. 

function DecimalTimeDiff(f,f1) {
    var strRslt = "";
    var rgTime =  /^\s*(\d{1,2})\:(\d{2})\s*$/;
    var aStart = rgTime.exec(f);
    var aFinish = rgTime.exec(f1);
    if(aStart && aFinish)
    {
       var a = (Number(aStart[1])*60)+Number(aStart[2]);
       var b = (Number(aFinish[0])*60)+Number(aFinish[1]);
       var num = (b-a);
       var hours = (num / 60);
       strRslt = util.printf("%02.2f",hours);
    }
    return strRslt;
}

[CJ54235]

Apologize for my delayed reply to you Thom as I had gotten pulled away from this project. The last two days I have been trying to research and teach myself the different components in your script with little luck. Bouncing around various websites and forums has my mind spinning.

I placed your script at the document level and tweaked the HoursMon custom calculation script to call the DecimalTimeDiff function as follows.

//////////CUSTOM CALCULATION//////////

//morning time calculated
var timestarted = this.getField("InMon").value;
var timefinished = this.getField("OutMon").value;

var output = DecimalTimeDiff(timestarted,timefinished);
event.value = output;

//afternoon time calculated
var timestarted2 = this.getField("InMon_2").value;
var timefinished2 = this.getField("OutMon_2").value;

var output = DecimalTimeDiff(timestarted2,timefinished2);
event.value = Number(event.value) + Number(output);

//////////END//////////

I see you have a util.printf at the document level, so I am presuming the Custom Format Script below for HoursMon cell needs tweaking or removal. I had this code there so if a time (either In or Out) was missing for the day, HoursMon remained blank.

event.value = (event.value != 0)?util.printf("%,0.2f",event.value):"";

I tried tweaking the code to:
event.value = (event.value != 0)?util.printf("%02.2f",event.value):"";

but receive 1.#R for a value. And if I remove the Custom Format Script from HoursMon, the value is now displayed as NaN.

I have updated the In and Out times since my first post to display as h:MM in the Format Time Custom so the 24-hr time entered is correctly displayed in 12-hr time. (ie 14:00 displays as 2:00). Still working on how to let user input 4-digits without a colon, but displays time with colon.

Appreciate your time with this novice user! I have uploaded the new file if it helps you at ttps://tinyurl.com/4y3numve

[Thom Parker]

First, remove the format scripts. Never format until after the calculations are worked out, because formatting obscures the results. 

The printf in your format script is returning "1.#R" because the input is not a number. This is why formatting is added last, so the real results are immediately visible. 

BTW: I set the output of the "DecimalTimeDiff()" function to be a decimal value because that is what you asked for.  

So now you want the user to enter a time value like 1.30 and have it interpreted as 1:30am? Did you want the "1.30" to replace the "1.30" in the field immediately after it is entered? What do you want the output to look like? Please be specific about how you want this to work. Give an example.

[CJ54235]

Greetings Thom,

I do want the Totals field to display time worked for the day as deimal. IE 2 hrs 30 minutes would display as 2.5 (not 2.3). I mistyped.

So I removed the Custom Format Script from HoursMon. I am to remove the h:MM Format from InMon, OutMon, InMon_2, and OutMon_2 also?

The below image is the old timesheet I am converting out of LiveCycle Designer. It allowed the user to enter miltary time for In and Out fields and the script converted the time to dislay in the 12-hr format. The reason for military time usage was to eliminate the need for AM/PM columns. The Reg. Hours calculation resulted in a decimal number and the totals for the week is a simple addition of fields in Reg Hours column.

Using above image, user entered:

0800 MorningIn; displays at 8:00

1200 MorningOut; displays 12:00

1300 AfternoonIn; display 1:00

1630 AfternoonOut; display 4:30

Note that when the cursor is clicked in the field, I see AM or PM. Not sure how that was done. Does this make my request clearer?

[Thom Parker · Accepted solution]

First, here is a correction to the time difference script. There was an error in the "b" calculation and I've changed the output to be a number, rather than a string, which seems in line with how you want to use it. 

function DecimalTimeDiff(f,f1) {
    var nHours = 0;
    var rgTime =  /^\s*(\d{1,2})\:(\d{2})\s*$/;
    var aStart = rgTime.exec(f);
    var aFinish = rgTime.exec(f1);
    if(aStart && aFinish)
    {
       var a = (Number(aStart[1])*60)+Number(aStart[2]);
       var b = (Number(aFinish[1])*60)+Number(aFinish[2]);
       var num = (b-a);
       nHours = (num / 60);
    }
    return nHours;
}

The way to convert numbers in place is to use a custom validation script:

var rgAltTime =  /^\s*(\d{1,2})\:?(\d{2})\s*$/;
var aMatches = rgAltTime.exec(event.value);
if(aMatches && (aMatches[0] < 24) && (aMatches[1] < 60) )
    event.value = aMatches[1] + ":" + aMatches[2];
else
    event.value = "";

Note that the "if" statement includes logic for validating the entered time value.

[CJ54235]

Thank you for the updates. I have updated the doc level DecimalTimeDiff code and entered the custom validation script for each individual time fields (InMon, OutMon, InMon_2, and OutMon_2). The validation script is the only thing being applied to each time field - no formatting or calculations. Banging my head here as the time entered (ie InMon) is not displaying after moving the cursor out of the field (field remains blank).

I tried swapping all the aMatches with field name (ie InMon) thinking I had to customize the validation script with the field name, however that doesn't appear to be the right answer either. It doesn't seem right that I should change those names as I believe the aMatches are referencing the numbers keyed in by the user. I tried adding a custom Time format of hh:MM to individual field along with the custom validation script with no luck. I do not get any errors in the JavaScript Debugger. What am I am doing wrong?

[Thom Parker · Accepted solution]

My bad,  here's an update to the validate script:

Note the only difference is the indexes used for the validation of  the aMatches entries.

var rgAltTime =  /^\s*(\d{1,2})\:?(\d{2})\s*$/;
var aMatches = rgAltTime.exec(event.value);
if(aMatches && (aMatches[1] < 24) && (aMatches[2] < 60) )
    event.value = aMatches[1] + ":" + aMatches[2];
else
    event.value = "";

[CJ54235]

THANK YOU, THOM! I wondered about the [0] for one of the aMatches, but being a novice didn't question it. I have updated the form and have the column addition of Totals working great. I am learning a great deal of scripting. For my knowledge in the rgAltTime expression, have I islosated the "\:?" as the code that allows the colon to be optional?

[Thom Parker]

That is correct. The "?" means 0 or 1 instances of the preceeding element. 

There are many web pages that cover the details of regular expressions.

There are some links at the bottom of this article:

https://www.pdfscripting.com/public/Pattern-Matching-with-Regular-Expressions.cfm

[CJ54235]

Thanks Thom! In fact, I have read that article and explored the links at bottom of page. All a tad overwhelming but the pages are book marked so I can go back to them and learn more. Thank you again for all your assistance!