Adding Time

  • Thread starter Thread starter Hockyplr
  • Start date Start date
H

Hockyplr

I have a schedule spreadsheet created. I need to know if the followin
can be done....

There is a cell for each day of the week, and in each cell I type i
the persons scheduled start and finish time, all in ONE CELL. Lik
this:

Sunday
9:00-6:00pm

Monday
10:00-8:00pm

Etc.

I am wondering if the one cell, with the times, can be added togethe
in another cell, so as I am making the schedule, I can look over to se
if each person has 40 hours per week.

So it would look at the cell containing 9:00-6:00pm and say that is
hours, and add that to the grand total of the week....

Any help would be MUCH appreciated
 
You're creating several handicaps for yourself.

1) You're using variable length times, which means that a string
function will have more to parse

2) You're using ambiguous times - is 1:00-2:00pm 1 hour or 13 hours?

3) XL won't recognize "6:00pm" as a time, using TIMEVALUE(), whereas
"6:00 pm" is.

If you make some limiting assumptions, such that the first time is
always in the morning, the times will never span midnight, then you
could use something like:

A1: 9:00-6:00pm
A2: =MOD(TIMEVALUE(SUBSTITUTE(SUBSTITUTE(MID(A1,FIND("-",A1)+1,255),
"0a","0 a"),"0p","0 p"))-TIMEVALUE(LEFT(A1,FIND("-",A1)-1)),1)
 
JE's formula is a lot shorter than mine, Mine would have been
= TIMEVALUE(MID(A8,FIND("-",A8)+1,LEN(A8)-2-FIND("-",A8)-1)&" "&RIGHT(A8,2))
- TIMEVALUE(MID(A8,FIND(CHAR(10),A8)+1,FIND("-",A8)-FIND(CHAR(10),A8)-1))

but to show you what additional problems and what
additional features you lose in your unconventional timesheet.

You make data entry difficult, and an extraneous space would
throw the whole thing off..

a formula such as =C1-B1+(B1>C1)
would be able to handle a time starting am or pm
and going through midnight as long as the time interval is less than 24 hours.
C1 is end time, B1 is start time,
(B1>C1) is a logical that adds 1 day if start time is greater than end time.

For more information on date and time see
http://www.mvps.org/dmcritchie/excel/datetime.htm
http://www.cpearson.com/excel/datetime.htm

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
 
I see what you guys are saying, and because of the limitations,
modfied the entire sheet...

Now the start and end times are in different cells, thus making it eas
to subtract.

Although I did hit a problem, here is the current formula I am using:

=24*(IF(D8>D9,D9+1-D8,D9-D8))

And it works great, except....

I don't like having blanks for days off, and since this is for a retai
market, we are not a Mon-Fri operation. We work seven days a week.

What I'd like to do is be able to put OFF in both cells (start and End
to designate days off. However, if I do that now, it creates an erro
in my formula,.

So my question now is, is there any way to do it?

Thanks again guys
 
Excellent! Thanks much. I modified it by taking out the "OFF" an
replacing that with just 0. That way any word in a cell will equa
Zero. And since I am adding the entire row, they need to be at leas
Zero.

Thanks again for the QUICK response!

Hockypl
 
Back
Top