I have used a google form for time worked and synced it with smartsheet. I then have a column on my sheet to work out how many hours have been worked on a particular day using the information supplied on the entry from the google form. For instance if Joe worked 09:00 to 17:00 and entered this on his google form submission I want my sheet to be able to work out how many hours Joe worked (17:00 - 09:00). Does anyone know the formula to use in my 'hours worked' column, I have applied the usual formula of =([start time]1)-([finish time]1) but it doesn't work I think because it doesn't recognise the symbol ( : ) part of the time they have entered.
Many thanks,