me needs some reminding (ms excel)

plthijinx · 04-26-2011, 03:54 PM

i have a schedule spreadsheet for work i made some time ago in excel. what i want to do, but forgot how, is this: when the times are entered into the fields i want excel to add up the total hours there on the far right column. can't remember how to do that. help anyone?

HungLikeJesus · 04-26-2011, 04:04 PM

The only way I know to do it would require entering the start and end time for each day in separate columns, preferably using 24 hour time. Then for each day, subtract the start time from the end time and add across.

BigV · 04-26-2011, 04:18 PM

right.

you can't do math with letters. you have to have the terms be numeric (even time is numeric, but not the way you're displaying it there)... HLJ's right (as usual).

plthijinx · 04-26-2011, 04:31 PM

that's what i thought but it's been so long since i've used formulas or excel for that matter, was hoping there was an easier way nowadays. thanks y'all!

HungLikeJesus · 04-26-2011, 04:37 PM

Let me know if you need any help.

HungLikeJesus · 04-27-2011, 04:36 PM

I sent you a simple Excel timesheet.

HungLikeJesus · 04-30-2011, 10:22 AM

I've updated it for shifts that extend beyond midnight. Check your e-mail.

skysidhe · 04-30-2011, 11:10 AM

In I 11 type = sum then highlight the entire column.

HungLikeJesus · 04-30-2011, 11:21 AM

The problem was calculating column 11 from the times entered in the columns to the left.

jimhelm · 04-30-2011, 11:57 AM

i dicked around with it, but i couldn't get it to do the math on the hours properly. then I saw that you had helped out so i stopped trying.

HungLikeJesus · 04-30-2011, 12:00 PM

Don't stop trying - my solution wasn't great. Maybe you can come up with something better.

It's always good to learn something new.

skysidhe · 04-30-2011, 12:16 PM

oops

Unfortunately, you'll need to add additional columns. Fortunately it is easy. Right click at the top to insert columns for time in time out.

Here is a guide.

http://www.techrepublic.com/article/...-shift/5756139

jimhelm · 04-30-2011, 12:54 PM

was just getting into this, and work interrupted...as usual. stupid work.

jimhelm · 04-30-2011, 02:27 PM

OK....

so the answer I needed was right at the end of that video.

Fred, You have to format the cells first.
skip two rows for label and header.

1. Highlight B3 over to O3 and down as many rows as you have people you want on your list.

2. right click, format cells, custom, and either select or manually enter this: h:mm am/pm (see attached image for this)

3. column A is for the emp name.

4. column b is mon in, c mon out, d tues in and so on out to column O sun out.

5. highlight column P and format like above, but put this in the Type field [h]:mm (the brackets allow your total to exceed 24 hours)

6. this is the formula for the total hours per week:

=SUM((C3-B3)+(E3-D3)+(G3-F3)+(I3-H3)+(K3-J3)+(M3-L3)+(O3-N3))

cut and past that into field P3

do you know how to drag the cross down from P3 to extend the formula to the adjacent cells?

jimhelm · 04-30-2011, 02:29 PM

when you fill in your fields, be sure to follow the format:

8:00 am or 2:30 pm with a space before the am/pm part

04-26-2011, 03:54 PM	#1
plthijinx Master Dwellar Join Date: Aug 2003 Posts: 4,197	me needs some reminding (ms excel) i have a schedule spreadsheet for work i made some time ago in excel. what i want to do, but forgot how, is this: when the times are entered into the fields i want excel to add up the total hours there on the far right column. can't remember how to do that. help anyone? Attached Images __________________ For your dreams to come true, you must first have a dream. Last edited by plthijinx; 04-26-2011 at 04:34 PM.

04-26-2011, 04:04 PM	#2
HungLikeJesus Only looks like a disaster tourist Join Date: Feb 2007 Location: above 7,000 feet Posts: 7,208	The only way I know to do it would require entering the start and end time for each day in separate columns, preferably using 24 hour time. Then for each day, subtract the start time from the end time and add across. __________________ Keep Your Bodies Off My Lawn SteveDallas's Random Thread Picker.

04-26-2011, 04:18 PM	#3
BigV Goon Squad Leader Join Date: Nov 2004 Location: Seattle Posts: 27,063	right. you can't do math with letters. you have to have the terms be numeric (even time is numeric, but not the way you're displaying it there)... HLJ's right (as usual). __________________ Be Just and Fear Not.

04-26-2011, 04:31 PM	#4
plthijinx Master Dwellar Join Date: Aug 2003 Posts: 4,197	that's what i thought but it's been so long since i've used formulas or excel for that matter, was hoping there was an easier way nowadays. thanks y'all! __________________ For your dreams to come true, you must first have a dream.

04-26-2011, 04:37 PM	#5
HungLikeJesus Only looks like a disaster tourist Join Date: Feb 2007 Location: above 7,000 feet Posts: 7,208	Let me know if you need any help. __________________ Keep Your Bodies Off My Lawn SteveDallas's Random Thread Picker.

04-27-2011, 04:36 PM	#6
HungLikeJesus Only looks like a disaster tourist Join Date: Feb 2007 Location: above 7,000 feet Posts: 7,208	I sent you a simple Excel timesheet. __________________ Keep Your Bodies Off My Lawn SteveDallas's Random Thread Picker.

04-30-2011, 10:22 AM	#7
HungLikeJesus Only looks like a disaster tourist Join Date: Feb 2007 Location: above 7,000 feet Posts: 7,208	I've updated it for shifts that extend beyond midnight. Check your e-mail. __________________ Keep Your Bodies Off My Lawn SteveDallas's Random Thread Picker.

04-30-2011, 11:10 AM	#8
skysidhe ~~Life is either a daring adventure or nothing.~~ Join Date: Apr 2006 Posts: 6,828	In I 11 type = sum then highlight the entire column.

04-30-2011, 11:21 AM	#9
HungLikeJesus Only looks like a disaster tourist Join Date: Feb 2007 Location: above 7,000 feet Posts: 7,208	The problem was calculating column 11 from the times entered in the columns to the left. __________________ Keep Your Bodies Off My Lawn SteveDallas's Random Thread Picker.

04-30-2011, 11:57 AM	#10
jimhelm a beautiful fool Join Date: Sep 2010 Location: 39.939705 Posts: 4,504	i dicked around with it, but i couldn't get it to do the math on the hours properly. then I saw that you had helped out so i stopped trying. __________________ There's a Shadow just behind me. Shrouding every step I take. Making every promise empty, pointing every finger at me. _tool

04-30-2011, 12:00 PM	#11
HungLikeJesus Only looks like a disaster tourist Join Date: Feb 2007 Location: above 7,000 feet Posts: 7,208	Don't stop trying - my solution wasn't great. Maybe you can come up with something better. It's always good to learn something new. __________________ Keep Your Bodies Off My Lawn SteveDallas's Random Thread Picker.

04-30-2011, 12:16 PM	#12
skysidhe ~~Life is either a daring adventure or nothing.~~ Join Date: Apr 2006 Posts: 6,828	oops Unfortunately, you'll need to add additional columns. Fortunately it is easy. Right click at the top to insert columns for time in time out. Here is a guide. http://www.techrepublic.com/article/...-shift/5756139

04-30-2011, 12:54 PM	#13
jimhelm a beautiful fool Join Date: Sep 2010 Location: 39.939705 Posts: 4,504	was just getting into this, and work interrupted...as usual. stupid work. __________________ There's a Shadow just behind me. Shrouding every step I take. Making every promise empty, pointing every finger at me. _tool

04-30-2011, 02:27 PM	#14
jimhelm a beautiful fool Join Date: Sep 2010 Location: 39.939705 Posts: 4,504	OK.... so the answer I needed was right at the end of that video. Fred, You have to format the cells first. skip two rows for label and header. 1. Highlight B3 over to O3 and down as many rows as you have people you want on your list. 2. right click, format cells, custom, and either select or manually enter this: h:mm am/pm (see attached image for this) 3. column A is for the emp name. 4. column b is mon in, c mon out, d tues in and so on out to column O sun out. 5. highlight column P and format like above, but put this in the Type field [h]:mm (the brackets allow your total to exceed 24 hours) 6. this is the formula for the total hours per week: =SUM((C3-B3)+(E3-D3)+(G3-F3)+(I3-H3)+(K3-J3)+(M3-L3)+(O3-N3)) cut and past that into field P3 do you know how to drag the cross down from P3 to extend the formula to the adjacent cells? Attached Images __________________ There's a Shadow just behind me. Shrouding every step I take. Making every promise empty, pointing every finger at me. _tool Last edited by jimhelm; 04-30-2011 at 02:46 PM.

04-30-2011, 02:29 PM	#15
jimhelm a beautiful fool Join Date: Sep 2010 Location: 39.939705 Posts: 4,504	when you fill in your fields, be sure to follow the format: 8:00 am or 2:30 pm with a space before the am/pm part __________________ There's a Shadow just behind me. Shrouding every step I take. Making every promise empty, pointing every finger at me. _tool

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)