How to capture time elements from a form and display on a dashboard.

Katie J

Good morning Experts,

I have a particular set of data that is tracking productivity with a time element reported as (hour):(minutes) AM or PM

I am gathering the data in a form using text/number. Once in the sheet/grid I cannot figure out If I can or how to auto populate the results for a single time element in my sheet summary to display on a dashboard. I believe it may be due to the nonnumerical characters of AM/PM interfering with the functions I have tried initially. I did simply select the individual cell and that will display, but when the sheet is cleared and new data arrives the sheet summary displays an #UNPARSEABLE error siting #REF.

Desired reports would include daily and an average time over a 30-day period. I'm sure I will run into more issues on my second sheet once I get there as well. Currently data is moved from primary location where the form gathers the data to a monthly sheet, then at the end of 30 days it is cataloged in a repository sheet.

Is this the best way to set this up? What formula would best work for time elements in a single day, or the average time over 30 days? Should I simply remove the AM/PM element of the data being collected?

Thank you for your assistance!

Katie

Paul Newcome

Even removing the am/pm element won't help on its own as Smartsheet does not yet have a built in function for calculating time.

Take a look through this thread for various solution to time calculations:

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time