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?
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.
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).
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!
Let me know if you need any help.
I sent you a simple Excel timesheet.
I've updated it for shifts that extend beyond midnight. Check your e-mail.
In I 11 type = sum then highlight the entire column.
The problem was calculating column 11 from the times entered in the columns to the left.
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.
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.
oops:o
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/use-excel-to-calculate-the-hours-worked-for-any-shift/5756139[YOUTUBE]MtClZ6meTFg[/YOUTUBE]
was just getting into this, and work interrupted...as usual. stupid work.
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 [COLOR="Red"](see attached image for this)[/COLOR]
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?
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
What happens if you put in a shift that runs beyond midnight (e.g. 1 PM to 1 AM)?
yeah, the file that HLJ sent me works just how i want. all i did was hide the daily total columns then i'm going to tweak it a little more.
new question:
msword. is there a way to print to a jpeg file? i know you can print to jpeg but it's been so dang long since i've worked with it so much has changed. kinda the same problem i had with excel......
Insert > picture> browse location
My apologies if I misunderstood the question, again.:blush:
If it's just a page or two you could do an alt-"print screen" and then paste back into a new document and crop out the extra part. (Or, if you're using Vista or newer, there's a snipping tool that lets you grab whatever part of the screen you want.)
You can also select print and print to a PDF (I don't know if that's included with Word, or if you have to have Adobe Writer/Pro).
Another option is to print to the XPS Document Writer.
yeah i did the print screen option. i did it's job. basically the GM at work had a logo for his pool league t-shirts and the printer needed a jpeg rather than the word file.
oh, and another thing, i've finished the time sheet. will post it later today or tomorrow. whenever i get the chance. most likely tomorrow.
here is the tweaked version. again thank you for all the help!!! this is exactly what i wanted!
Looks good. I like your use of color.
Looks good. I like your use of color.
ty, makes it easier to read and follow. easy contrast to the eyes....
What happens if you put in a shift that runs beyond midnight (e.g. 1 PM to 1 AM)?
then you have to rewrite the code and it takes like twice as long. he talks about it in that video.