I've got a Form where I have a table with 8 fields and 1 field that calculates total hours worked. Each field the user needs to enter a valid time 8:00 AM or 1700, which will be converted to 5:00 PM. I'm trying to come up with a FormCalc formula that will give me the total hours worked.
This is what I have so far;
(Row5.Cell2-Row4.Cell2+Row7.Cell2-Row6.Cell2+Row9.Cell2-Row8.Cell2+Row11.Cell2-Row10.Cell2)*1440/60
The fields are as follows:
Start <Row4.Cell2>
Stop <Row5.Cell2>
Start <Row6.Cell2>
Lunch Start <Row7.Cell2>
Lunch End <Row8.Cell2>
Stop <Row9.Cell2>
Start <Row10.Cell2>
End <Row11.Cell2>
Total Hours
All it does is keep giving me weird totals and it's never consistant.
Views
Replies
Total Likes
Hi,
the calculation of time differences is not trivial.
A few rules to know:
1: For the total hours you should not use a date/time field - use a text field instead.
It's because the date/time field will only accept values within a 24 hours time frame.
Larger values will cause unexpected results.
2: The times are calculated in milliseconds.
3: The times needs to be converted from their formattedValue into milliseconds using the Time2Num() method to be comparable.
Here's a similar solution I posted a while ago.
You can adapt this to your own needs.
Views
Replies
Total Likes
I tried to implement what you suggested, but now it just constantly shows a 0. Here is what I added to my FormCalc;
var fullTime = 0
if (Table1.Row4.Cell2 ne null and Table1.Row5.Cell2 ne null) then
if (Time2Num(Table1.Row4.Cell2.formattedValue, "HH:MM") lt Time2Num(Table1.Row5.Cell2.formattedValue, "HH:MM")) then
fullTime = Abs(Time2Num(Table1.Row4.Cell2.formattedValue, "HH:MM") - Time2Num(Table1.Row5.Cell2.formattedValue, "HH:MM")) / (60 * 60 * 1000)
else
fullTime = 24 - Abs(Time2Num(Table1.Row5.Cell2.formattedValue, "HH:MM") - Time2Num(Table1.Row4.Cell2.formattedValue, "HH:MM")) / (60 * 60 * 1000)
endif
endif
if (Table1.Row6.Cell2 ne null and Table1.Row7.Cell2 ne null) then
if (Time2Num(Table1.Row6.Cell2.formattedValue, "HH:MM") lt Time2Num(Table1.Row7.Cell2.formattedValue, "HH:MM")) then
fullTime = fullTime + Abs(Time2Num(Table1.Row6.Cell2.formattedValue, "HH:MM") - Time2Num(Table1.Row7.Cell2.formattedValue, "HH:MM")) / (60 * 60 * 1000)
else
fullTime = fullTime + 24 - Abs(Table1.Row7.Cell2(EndTime.formattedValue, "HH:MM") - Time2Num(Table1.Row6.Cell2.formattedValue, "HH:MM")) / (60 * 60 * 1000)
endif
endif
if (Table1.Row8.Cell2 ne null and Table1.Row9.Cell2 ne null) then
if (Time2Num(Table1.Row8.Cell2.formattedValue, "HH:MM") lt Time2Num(Table1.Row9.Cell2.formattedValue, "HH:MM")) then
fullTime = fullTime + Abs(Time2Num(Table1.Row8.Cell2.formattedValue, "HH:MM") - Time2Num(Table1.Row9.Cell2.formattedValue, "HH:MM")) / (60 * 60 * 1000)
else
fullTime = fullTime + 24 - Abs(Table1.Row9.Cell2(EndTime.formattedValue, "HH:MM") - Time2Num(Table1.Row8.Cell2.formattedValue, "HH:MM")) / (60 * 60 * 1000)
endif
endif
if (Table1.Row10.Cell2 ne null and Table1.Row11.Cell2 ne null) then
if (Time2Num(Table1.Row10.Cell2.formattedValue, "HH:MM") lt Time2Num(Table1.Row11.Cell2.formattedValue, "HH:MM")) then
fullTime = fullTime + Abs(Time2Num(Table1.Row10.Cell2.formattedValue, "HH:MM") - Time2Num(Table1.Row11.Cell2.formattedValue, "HH:MM")) / (60 * 60 * 1000)
else
fullTime = fullTime + 24 - Abs(Table1.Row11.Cell2(EndTime.formattedValue, "HH:MM") - Time2Num(Table1.Row10.Cell2.formattedValue, "HH:MM")) / (60 * 60 * 1000)
endif
endif
$ = fullTime
Views
Replies
Total Likes
Maybe if I gave you an example it might help.
Start 8:00 AM
Stop
Start
Lunch Start 12:00 PM
Lunch End 1:00 PM
Stop
Start
End 5:00 PM
Total Hours 8.0
OR
Start 7:00 AM
Stop
Start
Lunch Start 12:00 PM
Lunch End 1:00 PM
Stop
Start
End 6:00 PM
Total Hours 10.0
Views
Replies
Total Likes
I've noticed that it might have to deal with the seconds. When you try to do calculations they need the seconds for it to work, but when a user enters 8:00 AM there's no way to add the 00 to the end of the 8:00. To do calculations you need it in the format of 8:00:00 AM.
Views
Replies
Total Likes
There is no need to enter the seconds to the time fields to calculate with times.
But it's neccessary to use the correct time pattern in the script.
The same mentioned above uses the pattern HH:MM to parse the formattedValue.
If your time fields uses another pattern like HH:MM A (01:23 AM) to display the time you have to change it in the entire script otherwise the returned value is always 0.
if (Table1.Row4.Cell2 ne null and Table1.Row5.Cell2 ne null) then
if (Time2Num(Table1.Row4.Cell2.formattedValue, "HH:MM A") lt Time2Num(Table1.Row5.Cell2.formattedValue, "HH:MM A")) ...
Views
Replies
Total Likes
I have done what you've suggested and the input fields are set to time with a format of time{h:MM A}. It seems to just not calculate with this.
var fullTime = 0
if (Table1.Row4.Cell2 ne null and Table1.Row5.Cell2 ne null) then
if (Time2Num(Table1.Row4.Cell2.formattedValue, "HH:MM A") lt Time2Num(Table1.Row5.Cell2.formattedValue, "HH:MM A")) then
fullTime = Abs(Time2Num(Table1.Row4.Cell2.formattedValue, "HH:MM A") - Time2Num(Table1.Row5.Cell2.formattedValue, "HH:MM A")) / (60 * 60 * 1000)
else
fullTime = 24 - Abs(Time2Num(Table1.Row5.Cell2.formattedValue, "HH:MM A") - Time2Num(Table1.Row4.Cell2.formattedValue, "HH:MM A")) / (60 * 60 * 1000)
endif
endif
if (Table1.Row6.Cell2 ne null and Table1.Row7.Cell2 ne null) then
if (Time2Num(Table1.Row6.Cell2.formattedValue, "HH:MM A") lt Time2Num(Table1.Row7.Cell2.formattedValue, "HH:MM A")) then
fullTime = fullTime + Abs(Time2Num(Table1.Row6.Cell2.formattedValue, "HH:MM A") - Time2Num(Table1.Row7.Cell2.formattedValue, "HH:MM A")) / (60 * 60 * 1000)
else
fullTime = fullTime + 24 - Abs(Table1.Row7.Cell2(EndTime.formattedValue, "HH:MM A") - Time2Num(Table1.Row6.Cell2.formattedValue, "HH:MM A")) / (60 * 60 * 1000)
endif
endif
if (Table1.Row8.Cell2 ne null and Table1.Row9.Cell2 ne null) then
if (Time2Num(Table1.Row8.Cell2.formattedValue, "HH:MM A") lt Time2Num(Table1.Row9.Cell2.formattedValue, "HH:MM A")) then
fullTime = fullTime + Abs(Time2Num(Table1.Row8.Cell2.formattedValue, "HH:MM A") - Time2Num(Table1.Row9.Cell2.formattedValue, "HH:MM A")) / (60 * 60 * 1000)
else
fullTime = fullTime + 24 - Abs(Table1.Row9.Cell2(EndTime.formattedValue, "HH:MM A") - Time2Num(Table1.Row8.Cell2.formattedValue, "HH:MM A")) / (60 * 60 * 1000)
endif
endif
if (Table1.Row10.Cell2 ne null and Table1.Row11.Cell2 ne null) then
if (Time2Num(Table1.Row10.Cell2.formattedValue, "HH:MM A") lt Time2Num(Table1.Row11.Cell2.formattedValue, "HH:MM A")) then
fullTime = fullTime + Abs(Time2Num(Table1.Row10.Cell2.formattedValue, "HH:MM A") - Time2Num(Table1.Row11.Cell2.formattedValue, "HH:MM A")) / (60 * 60 * 1000)
else
fullTime = fullTime + 24 - Abs(Table1.Row11.Cell2(EndTime.formattedValue, "HH:MM A") - Time2Num(Table1.Row10.Cell2.formattedValue, "HH:MM A")) / (60 * 60 * 1000)
endif
endif
$ = fullTime
Views
Replies
Total Likes
Any thoughts?
Views
Replies
Total Likes
Hi,
could you please upload your form anywhere and post a link to it here? It's hard to unserstand how your form is designed without seeing it.
Views
Replies
Total Likes
Views
Likes
Replies