Time Formats and formulas

[Toni]

what i wish to do is have an electronic version of a
rostering system for a business. what i need to do is have
a row which displays total hours worked, then total hours
selling (so minus luch and set up times). however,
whenever i go to input a time into a formula the syntax of
time is e.g.(10:15) where the colon is necessary. However,
the colon in the formula is read as a cell reference thus
refering to e.g.(10:15) as everything inbetween cells 10
and 15 of all rows. does anyone have an idea as to how i
could solve the syntax error without undoing my time
format, as this is a roster which needs to be printed for
the staff.

Regards,
toni

 

[David McRitchie]

Hi Toni,
Normally you would pick up times from the spreadsheet.
="10:15" + "0:45" format as time i.e. [h]:mm

 

[jthomasd8]

Toni, I'm not sure about what you're asking but, this might help. I created a 28 day time schedule for 560 employeesand ran into syntax problems, too. Solution--I formatted cell A1 for names, B1 (for the time employee came to work), C1 (time the employee went to lunch), D1(came back to work), E1(time off work), and F1 total time. Format B1-E1 with time function 1300 & F1 36:00:00. Formula in F1 then is simple addition =SUM (C1-B1)+(E1-D1). Hope it helps.