How to get a numeric value of the last Friday of the month

[kkamakasi]

I am simplifying my excel sheet formatting just so I can ask the essential question..

I have a form that I update daily with market price of a certain commodity.
This form has three columns.

Row 2 Column A: Price on the last Friday of previous month
Row 2 Column B: Yesterday's price
Row 2 Column C: Today's price

I have a macro setup that I execute everyday around 5PM that does the following: Takes the value in Row 2 Column C and copies into Row 2 Column B and clears Row 2 Column C for today's price.

However, when it is a new month, I have to always update Row 2 Column A manually.

Is there a way for my macro to determine if today is the first Monday of the month and if it is, copy and paste the values in Row 2 Column B as well as Row 2 Column A?

I hope this makes sense...

TIA

 

[mikerickson]

=IF(AND(TEXT(TODAY(),"ddd")="Mon", DAY(TODAY())<8, "First Monday of the month", "other")

 

[kkamakasi]

=IF(AND(TEXT(TODAY(),"ddd")="Mon", DAY(TODAY())<8, "First Monday of the month", "other")

Thank you for the response.

I pasted the below into my MACRO but it gives me an error.. (Cell N2 contains a date)

If Text("N2", "ddd") = "Mon" And Day("N2") < 8 Then

What am I doing wrong here?

 

[mikerickson]

You need to reference the value of the cell N2, not the string "N2".
Also, Format is the VBA equivalent of the worksheet function TEXT.

If Format(Range("N2").Value, "ddd") = "Mon" And Day(Range("N2").Value) < 8 Then

 

[kkamakasi]

Re: How to get a numeric value of the last WEEKDAY of the month

I actually asked the wrong question in my original post..

What I meant to ask was relating to last WEEKDAY of the month and/or first WEEKDAY of the month..

So I ask again as below:

I am simplifying my excel sheet formatting just so I can ask the essential question..

I have a form that I update daily with market price of a certain commodity.
This form has three columns.

Row 2 Column A: Price on the last WEEKDAY of previous month
Row 2 Column B: Yesterday's price
Row 2 Column C: Today's price

I have a macro setup that I execute everyday around 5PM that does the following: Takes the value in Row 2 Column C and copies into Row 2 Column B and clears Row 2 Column C for today's price.

However, when it is a new month, I have to always update Row 2 Column A manually.

Is there a way for my macro to determine if today is the first WEEKDAY of the month and if it is, copy and paste the values in Row 2 Column B as well as Row 2 Column A?

I hope this makes sense...

TIA

 

[kkamakasi]

Re: How to get a numeric value of the last WEEKDAY of the month

bumpp
anyone?