Timecards, hours & minutes
 

I am trying to create a simple timesheet for people who work approximately
7:30 or 8:00am until 5,6, or 7pm. I need an employee to choose (from a
dropdown data validation list) the time they start, go to lunch, come back
from lunch, and then leave the job. I need to use am/pm - not the 24hr
military times. I have 2 problems: #1: Should the dropdown contain all
variations to 15 minute intervals (ie 7:00am, 7:15am, etc resulting in 48
options!) OR can they choose from 2 cells - - one has hours (12 choices) and
one has minutes with 4 choices (0,15,30,45)? The least amount I need is down
to 15 minutes. I have tried to do this but then I cannot re-assemble the
result to, say, 7:15. #2: I need to do the math on the hours/minutes.
{(Time out to lunch - Time IN) + (Time out - Time back from lunch) = Total
hours worked that day}. Ideas/solutions on either problem would be
appreciated.

1) I would go for the one dropdown, easier to use, but there must be some
times that are not valid, so you could exclude these. In fact you could have
different lists for the different times, sign-in, lunch-out, lunch-back,
sign-out.

Just had a thought. If you put all the 15 min intervals in a list say
AA1:AA96, you could restrict teh DV to a number of times around now using a
List formula of

=OFFSET($AA$1,MATCH(MOD(NOW(),1),$AA:$AA,1)-2,,5)

2) Math works fine on Math, it is only a fraction of one day. If you want to
get as decimal, multiply by 24.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dixie" wrote in message
...
I am trying to create a simple timesheet for people who work approximately
7:30 or 8:00am until 5,6, or 7pm. I need an employee to choose (from a
dropdown data validation list) the time they start, go to lunch, come back
from lunch, and then leave the job. I need to use am/pm - not the 24hr
military times. I have 2 problems: #1: Should the dropdown contain all
variations to 15 minute intervals (ie 7:00am, 7:15am, etc resulting in 48
options!) OR can they choose from 2 cells - - one has hours (12 choices)
and
one has minutes with 4 choices (0,15,30,45)? The least amount I need is
down
to 15 minutes. I have tried to do this but then I cannot re-assemble the
result to, say, 7:15. #2: I need to do the math on the hours/minutes.
{(Time out to lunch - Time IN) + (Time out - Time back from lunch) = Total
hours worked that day}. Ideas/solutions on either problem would be
appreciated.