Formulas to calculate data received from web form

ATCC Administration

This is a screenshot of my page that I want to try and get the formulars for. The cells that are in yellow, the data in those cells will come from my staff that I have complete the web form that i have attached to this sheet. The red cells are the ones that I require the formulas for to calculate the different times that I require.

Any help with this would be greatly appreciated!

 

Thank you!

J. Craig Williams

First, be aware that native webforms in Smartsheet do not update existing rows, only add new ones. If you are expecting / hoping that the WebForm usage will be:

 

1. user enters [StartTime] - via WebForm

2. user works until first break

3. user enters [FirstBreakStartTime] - via WebForm

etc...

then unless the user has the same form open the whole day/shift and does not hit the Submit button until ready to enter [WorkFinishTime], you'll need to look elsewhere for that form.

(Having them enter data periodically during the day/shift and then hitting Submit is likely prone to error, in my opinion/experience)

 

Second, I'm assuming the user entered data is entering the data from a drop-down list.

If not, the parsing can be complicated and prone to user data entry errors.

 

If you are using drop-downs, then the simplest system would change the text:

 

1:00am 

 

to 

 

05 - 1:00am

 

where the number is 15 minute increments from midnight. The padding to two digits is to simplify the calculation.

 

Assuming work is from midnight (01 - 12:00am) to midnight (97 - 12:00am)

 

Then you can just determine the [TotalWorkHours] by

 

=(VALUE(LEFT(WorkFinishTime1, 2)) - VALUE(LEFT(StartTime1, 2))) * 15 / 60

 

which will give you a decimal value in hours.

If you absolutely need it be in hh:mm format, that is a little harder but can also be done.

 

The break time calcs would be similar.

 

Smartsheet does not add time without come coaxing.

 

Hope this gets you started in the right direction.

Craig