Inserting dates

[murfitUK]

Is this possible?

I have two cells which are filled in by choosing from drop-down lists. A1
is January, February etc and B1 is 2006, 2007 etc.

Once the user chooses the month & year I would like Excel to automatically
fill in some fields further down the sheet - showing the Friday dates in the
month/year chosen.

EG

Choosing March 2007 would give:
Friday 2
Friday 9
Friday 16
Friday 23
Friday 30

or August 2006 would give:
Friday 4
Friday 11
Friday 18
Friday 25
(and then a blank cell as only 4 Fridays in this month)

I can type "Friday" in as text in eg A15-A19 and then use conditional
formatting to hide the A19 Friday if there are only 4 Fridays in the month -
I will do this by turning the font colour to white if B19 is empty. That's
the easy part!

But... Is there any formula to calculate the day numbers to go into B15-B19?

All help/suggestion gratefully received.

Thanks.

 

[Biff]

Hi!

Since you're not using a full date format from either drop down you'll have
to create a little table so that you can generate a date. You'll see in the
final formula why this is necessary.

Create a list of the month names:

G1 = January
G2 = February
...
G12 = December

You may already have a list like this that you use as the source for month
drop down.

Select cells A15:A19 and give them a custom format of: dddd d.

Enter this formula in A15 and copy down to A19:

=IF(TEXT(DATE(B$1,MATCH(A$1,G$1:G$12,0),1+1*ROWS($1:1)*7)-WEEKDAY(DATE(B$1,MATCH(A$1,G$1:G$12,0),8-6)),"mmmm")=A$1,DATE(B$1,MATCH(A$1,G$1:G$12,0),1+1*ROWS($1:1)*7)-WEEKDAY(DATE(B$1,MATCH(A$1,G$1:G$12,0),8-6)),"")

Kinda ugly, ain't it?

We can shorten it a little by replacing the references to MATCH with a cell
reference. That's what you needed the table for. For example, use a cell to
hold the month number, say, cell C1:

=MATCH(A1,G1:G12,0)

Then you can refer to that cell instead of using the MATCH inside the DATE
function:

=IF(TEXT(DATE(B$1,C$1,1+1*ROWS($1:1)*7)-WEEKDAY(DATE(B$1,C$1,8-6)),"mmmm")=A$1,DATE(B$1,C$1,1+1*ROWS($1:1)*7)-WEEKDAY(DATE(B$1,C$1,8-6)),"")

Another way to do this would be to use 2 different formulas, one formula to
find the first Friday of the selected month/year in cell A15 and then in the
subsequent cells, A16:A19, have a differnt formula that adds 7, 14, 21 or 28
days (if needed).

I like the "one formula fits all" method, myself.

Biff

 

[Ron Rosenfeld]

Is this possible?

I have two cells which are filled in by choosing from drop-down lists. A1
is January, February etc and B1 is 2006, 2007 etc.

Once the user chooses the month & year I would like Excel to automatically
fill in some fields further down the sheet - showing the Friday dates in the
month/year chosen.

EG

Choosing March 2007 would give:
Friday 2
Friday 9
Friday 16
Friday 23
Friday 30

or August 2006 would give:
Friday 4
Friday 11
Friday 18
Friday 25
(and then a blank cell as only 4 Fridays in this month)

I can type "Friday" in as text in eg A15-A19 and then use conditional
formatting to hide the A19 Friday if there are only 4 Fridays in the month -
I will do this by turning the font colour to white if B19 is empty. That's
the easy part!

But... Is there any formula to calculate the day numbers to go into B15-B19?

All help/suggestion gratefully received.

Thanks.

Use these formulas:

A15:
=IF(ISERR(DATEVALUE(A1 & " " & B1)),"",DATEVALUE(
A1 & " " & B1)+7-WEEKDAY(DATEVALUE(A1 & " " & B1)+1))

A16: =IF(ISERR(A15+7),"",A15+7)
copy/drag down to A18

A19: =IF(ISERR(A18+7),"",IF(MONTH(A18+7)=MONTH(A18),A18+7,""))

Select the range and custom format as "dddd d"

Format/Cells/Number Custom Type: "dddd d"

This will give you the result in a single cell.

If you want it in two cells, you could, for example, change the format to
"dddd".

Then,

B15: =A15

Copy/drag down to B19

Custom format as "d"




--ron

 

[Biff]

DATEVALUE(A1 & " " & B1)

Never saw that one before!

That can be very useful, as you've demonstrated.

Biff

 

[hansyt]

You could try this:

november 2007

Friday 2
Friday 9
Friday 16
Friday 23
Friday 30

Formula in B3:

=1+(5-WEEKDAY("1."& $A$1&" "&$B$1;2))+7*(WEEKDAY("1."& $A$1&"
"&$B$1;2)>5)

Formula in B4 and copied down to B7:

=(B3+7)*(DAY(EOMONTH("1."&$A$1&" "&$B$1;0))>=B3+7)

Hans

 

[Ron Rosenfeld]

Never saw that one before!

That can be very useful, as you've demonstrated.

Biff

It's especially useful when, as in this instance, the dates are presented in an
unambiguous fashion. The function will (again in this instance), as written,
return the first of the month.
--ron

 

[murfitUK]

To Ron and Biff...

I tried both your methods, and copied & pasted the formulas (formulae?) just
as you said. And they both worked first time!

I have absolutely no idea what your formulas mean or how you managed to
worked them out so I am in awe of your skills.

Thank you both very much.

To Hans. I got an error with your formula - something to do with $B$1.
Sorry. But thanks for trying to help.

 

[hansyt]

Right,

it's to do with the German settings. You probably need to replace the
semicolons with commas. And maybe it doesn't like the German date "1.
February 2006".

Sorry for that.

Hans