Excel Help

euro_scene

euro_scene

euro_scene

Permabanned
Joined
27 Nov 2009
Posts
306
down the left hand side I have a list of some tasks I have completed

and at the top I need the dates from the beginning of sept to the current date.. but I only want the dates on the days of Monday and Thurs

is there a quick way of doing that?

thanks
 
Put the first Monday you want, say 2010-01-11, in to cell B1. Then in cell C1 put the formula:

=IF(WEEKDAY(B1)=2,B1+3,B1+4)

This will give you the date for the next Thursday. Then, replicate the formula to cell D1 - this will give you the date for the next Monday. Keep replicating until you have enough dates :)

The formula is basically assessing if the date to the left of it is a Monday [where weekday() = 2], and if it is, adding the days till Thursday (+3 days), or if not (and thus it's a Thursday), adding the days till the next Monday (+4 days).
 
So, for an imaginary example, you want Monday 1 September, Tuesday 2 September, Wednesday 3 September, Thursday 4 September, Monday 8 September, etc?

Do it with an if statement. It'll be something like if(day($cell_to_the_left_of_this_one)=Thursday, $cell_to_the_left_of_this_one+3, $cell_to_the_left_of_this_one+1)

Obviously you'll need to put in the proper values for $cell_to_the... and you'll need to confirm the existence of the day() function, but there'll be some function like that.

EDIT: See above, for the correct functions. Also, I thought you said Monday to Thursday, not Monday and Thursday. Oops.
 
Have another row with Monday, Tuesday, Wednesday etc. (use autofill after entering the first Monday and it'll do it for you).
Fill in all the dates also using the drag handles.
Next, sort the dates and days together, deleting all the Fridays, Saturdays and Sundays.
Now re-sort by date order.

Have a cup of tea and look smug.

Edit: or, do that ^, which is cleverer.
 
euro_scene said:
yh thanks

I put this in B1 07/09/2009

then in C1 I put =IF(WEEKDAY(B1)=2,B1+3,B1+4)

nothing happend
Click to expand...

It works, just remember you've got to format the cell as a date, otherwise you'll just get the numerical representation of the date, which would be 40,000 or so. (1900 date system which counts upwards, 01/01/1900 being date number 1).
 
Select cell C1, then go to menu:tools:formula auditing:evaluate formula

What does the formula evaluate to? Where does it fail?
 
This is quite an interesting challenge and here is my solution:

In Cell a1 put this formula:
=TEXT(DATE(2009,8,31),"dddd, dd mmm yyyy")
In Cell b1 (and every other column to the right) put this formula:
=TEXT(RIGHT(A1,11)+IF(ISODD(COLUMN()),4,3),"dddd, dd mmm yyyy")

You can drag and paste right the formula in b1 across and right until you are satisfied. This will give you:
A B C
Monday, 31 Aug 2009 Thursday, 03 Sep 2009 Monday, 07 Sep 2009

Is this what you were looking for?
 
Formulae solutions such as Euroscene's are neat and satisfying. However remembering that dates are numbers you don't really need them.
Why not simply put the first two dates in (I'll assume in B1 and C1) then in D1 have the formula = B1+7. Copy this across.

This assumes that you only want columns on Mondays and Thursdays. If you need a column for each day but the date only in the columns for Mon/Thur then
create your first week manually.
For the second week put formula similar to above in monday and Thursday.
Copy the second week and paste to the right to create the third week etc. (After creating weeks 2 to 5 you could copy those formulae so you are copying 4 weeks at a time.
 
You must log in or register to reply here.
Back
Top Bottom