Execl help required - removal of weekends?

[neejah]

Simple one, I'm struggling with a bit - currently we do schedules in Excel and say it takes 8 hours to do a certain task I have converted this into a percentage (or decimal point of a week) eg. 8 hours = 0.2 of a working week, this is then added onto the start date e.g.

Start Date Monday 1/1/07
Exercise 1 = 0.5
Date Tuesday 2/1/07 Exercise 2 = 0.5
Date Wednesday 3/1/07 Exercise 3 = 1.0

But, when I copy the cells (to fill the schedule) it assumes a 7 day week, my question is how I remove saturday and Sunday? It's easy if using a fill option, and I could write an IF command but this would then leave blank rows in the schedule I'd I'd still need to mess about manually?

I'm sure they're must be an easy solution to this one?

[FunkyGibbon]

search the help for the NETWORKDAYS function - that will remove weekends and holidays.

edited to add you need to install and load the Analysis ToolPak add-in

FG

Edited by FunkyGibbon on Thursday 1st March 11:06

[eyebeebe]

Type Monday in row one. Tuesday in two etc to Friday. Highlight the five days. Go to tools, options, custom lists (tab). Click import. Fill down.

Bob's your aunt's husband.

Hmm I think I misunderstood the question. Ignore me

Edited by eyebeebe on Thursday 1st March 11:12

[neejah]

FG,
Thanks for that, but I'd looked at networkdays, it just gives you the number of days (assuming a finish date) if I'm not mistaken?

I'm after the following format really -

DATE TASK HOURS
Monday 1/07 Ex1 8
Tuesday 2/07 Ex2 8
Wednesday 3/07 Ex3 8
Thursday 4/07 Ex4 8
Friday 5/07 Ex5 8
Monday 8/07 Ex6 8

[FunkyGibbon]

ah right then.

create 5 entries like

Mon 05 Mar 2007
Tue 06 Mar 2007
Wed 07 Mar 2007
Thu 08 Mar 2007
Fri 09 Mar 2007

then highlight them - you will see a little black box bottom right.

right-click and hold and drag this selection as far down as you need and release mouse button - you will see a few fill options - one is weekdays only.

edited to add - search the help for "Create a consecutive list of dates" for perhaps a better description of how to do it.

Edited by FunkyGibbon on Thursday 1st March 11:23

[neejah]

FG,
Thanks again, but it's not specifically a fill command as the test schedule is not the same i.e. some tests/excercises will take say 2 hours, and some will take 5 hours - it depends on the schedule for the day. (if you know what I mean)
After a bit a trawling on the web I've managed to adapt the "weekday" function with a couple of IF statements.
Thanks again though.......

This is what I've ended up with, where B8 is the start date and assuming a 37 hour week over 5 days =IF(WEEKDAY(B8)=7,B8+2,IF(WEEKDAY(B8)=6,B8+3,B8+((W9/37)*5)))

[thewave]

neejah said:

Simple one, I'm struggling with a bit - currently we do schedules in Excel and say it takes 8 hours to do a certain task I have converted this into a percentage (or decimal point of a week) eg. 8 hours = 0.2 of a working week, this is then added onto the start date e.g.

Start Date Monday 1/1/07
Exercise 1 = 0.5
Date Tuesday 2/1/07 Exercise 2 = 0.5
Date Wednesday 3/1/07 Exercise 3 = 1.0

But, when I copy the cells (to fill the schedule) it assumes a 7 day week, my question is how I remove saturday and Sunday? It's easy if using a fill option, and I could write an IF command but this would then leave blank rows in the schedule I'd I'd still need to mess about manually?

I'm sure they're must be an easy solution to this one?

Neejah,

send me an example of the spreadsheet, one with what you have and then show me what result you want , and i'll see what I can do, as I'm not 100% sure what you're trying to do. [e-mail]

Regards

Clint

Edited by thewave on Thursday 1st March 15:54