Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

First Day of Month Formula Excel 2

Status
Not open for further replies.

bajo71

Programmer
Aug 6, 2007
135
US
Hello,

I am looking to combine the following two formulae into one..

=DATE(YEAR(A1),MONTH(A1),1) 'This yields date - 10/1/2009
=WEEKDAY(B1)'This gives me an integer - 5

But I cannot figure out how to combine the logic into one formula.

Any help is greatly appreciated.

-Mick
 
Combining formulae is straightforward - just replace the [blue][tt]B1[/tt][/blue] in your second formula with your first formula, the formula in B1:

[blue][tt]=WEEKDAY(DATE(YEAR(A1),MONTH(A1),1))[/tt][/blue]

It is, however, probably slightly easier in this case to do something like:

[blue][tt]=WEEKDAY(A1-DAY(A1)+1)[/tt][/blue]


Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website
 
=WEEKDAY(EOMONTH(A1,-1)+1) may work as well

A man has only two choices: He can be right or he can be happy.
 
Many thanks! All proposed formulae work.
 
or

[tab]=Weekday(Today-Day(Today())+1)

(Today() can be replaced with A1)

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.
 
Wow. In my defense, I was reading the post on my cell phone. I must have missed that somehow.

Carry on....

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.
 
I must be missing something, I am still trying to figure out exactly what the purpose is for this formula. When I run it all I get is the number 5.

Richard S. Anderson, RCDD
 
The core of the formula returns the first day of the month.

WeekDay returns the.... weekday of the date. So Oct 1 2009 is 5, which is Friday.

Have a look at Excel's help file on the WeekDay worksheet funciton.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.
 
BTW, Friday is the 6th day, since it runs Sunday through Saturday. To simply return the day of the week all you need is:
=WEEKDAY(A1)

Richard S. Anderson, RCDD
 
Servamatic said:
I understand that, but when I copy it down sequentially it gives me the number 5 regardless of the date.

Regardless of the date in October 2007, true, because they all have the same 1st October. How far down did you copy it?

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website
 
Servamatic,

The purpose of deriving a numeric is that I am constructing a custom calendar that works from Sunday to Saturday. So for instance, Sunday Sept 27th was day 1 week 1 in my calendar and as such I use the formula derived numeric in subsequent formulae that dynamically keep track of the weeks. So a formula to calculate numeric week using system calendar would read

IF(DAY(B3)<8,1,IF...

but in my situation I use(assuming derived number is in B1)

IF(DAY(B3)+($B$1-1)<8,1,IF...
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top