Please excuse my ignorance, I've very new to FMP.  I've searched the forum with little success.

Here's my problem: I've imported a .cvs of timesheet data and need to format the data and fields so when viewing the records it's easy to read. I have field 'day1StartTimeInSeconds' that contains a start time in seconds '32400' I'd like to show that as the actual time 9:00 when viewing the record but I can't figure out how.

I've tried formatting the data as text, number, and time with the option calculated value 'GetAsTime (day1StartTimeInSeconds)'. Formatting the data as time is the only way I can change the time option is the Data Formatting section of the inspector. But the results always show as 12:00 AM regardless of the data value. Any help would be appreciated, especially some detailed help.

 

Thanks!
Jim

Assuming the `day1StartTimeInSeconds` is a Number field, define a new calculation field with the result of Time and use =

day1StartTimeInSeconds

as the formula.

If this is a one-time conversion, you can then change the calculation field to a Time field and delete the original 'day1StartTimeInSeconds' field.

 

 

Thank you! I didn't realize I'd have to create a new field just to display the number as a time, but it worked. Now just to do that 20 more times to display all the In/Out/Lunch totals for each day of the week.

12 minutes ago, Jims1973 said:

Now just to do that 20 more times to display all the In/Out/Lunch totals for each day of the week.

Clearly, you have a structural issue here (I suspected as much when I saw the "day1" part in the field's name). You should have a separate record for each day. Then you only need to do this once for each type (i.e three times in total, IIUC).

Just now, comment said:

Clearly, you have a structural issue here (I suspected as much when I saw the "day1" part in the field's name). You should have a separate record for each day. Then you only need to do this once for each type (i.e three times in total, IIUC).

I don't disagree! I'm working with a .cvs export of data from a proprietary CRM system that we've been using for 7 years that's no longer supporting our business model. We used a company's solution who then sold it to another company who was much larger and no longer wanted to support a small business. After MONTHS of back and forth, I was able to get them to agree to an export of our historical data. Data that we need to reference regularly. So here I am trying to take what I got and make it something Sales and Admin staff can read.

Thanks again for your help, just seeing(understanding) the data a bit differently now from your comments has helped me figure out some other issues.

Jim