Formula Help (Calendar)

[joebean4jp]

I have a calendar that was built for me. Across one row I have "Mon, Tue, Wed, etc." I have the following formula that I am using which works fine for each cell...

=TEXT(WEEKDAY(DATE(CalendarYear,4,1),1),"aaa")

With this formula it will recognize that on the "1st of April" it will read "Mon" for that cell. The problem is, I have 12 tabs for each month. I do not know a shortcut that will let me copy and paste the formula across the row while increasing each day. For example...

Cell one (Mon) would be =TEXT(WEEKDAY(DATE(CalendarYear,4,1),1),"aaa"), cell two (Tue) would be =TEXT(WEEKDAY(DATE(CalendarYear,4,2),1),"aaa")

I am looking for a shortcut that would save me time to increase the day (red number) in the formula across the row without me manually manipulating each cell, for each day, for every month.

Hopefully someone understands my question. I appreciate any help.

 

[DRSteele]

Welcome to the forum.

Replace your red number in the left-most of the columns in your row with this and copy rightways: [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=COLUMNS($A$1:A1) Here, my left-most column is A, but yours is likely different.[/FONT]

 

[joebean4jp]

I'm definitely not understanding. My first day "Mon" is located on E2 and goes through AH2. Do I go to E2 and add to my formula and if so what does that look like...?

=TEXT(WEEKDAY(DATE(CalendarYear,4,1,1),"aaa")=COLUMNS($E$2:AH2)

?

 

[joebean4jp]

Once again, I appreciate your help and patience

 

[jtakw]

Hi,

What DRSteele means is:

Replace your red number in the left-most of the columns in your row with this and copy rightways: =COLUMNS($A$1:A1) Here, my left-most column is A, but yours is likely different

So your formula in your OP starting at E2 would look like this:

=TEXT(WEEKDAY(DATE(CalendarYear,4,COLUMNS($E2:E2)),1),"aaa")

However, your WEEKDAY function isn't really needed, and you can just do this:

Book1
	E	F	G	H	I	J	K	L	M
2	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue
Sheet647

Cell Formulas
Range	Formula
E2	=TEXT(DATE(2019,4,COLUMNS($E2:E2)),"ddd")

Replace my formula "2019" with "CalendarYear", so your formula in E2 would be this:

=TEXT(DATE(CalendarYear,4,COLUMNS($E2:E2)),"ddd")

Formula copied across.

 

[DRSteele]

I think you need to put this in E2 and copy across.

=TEXT(WEEKDAY(DATE(CalendarYear,4,COLUMNS($E$2:E2),1),"aaa")​

The $ make the cell absolute references. Here, we want it to read $E$2:E2 in the first cell, and when copied across the range will expand, showing $E$2:F2, then $E$2:G2 etcetera until you reach $E$2:AH2.

 

[joebean4jp]

DRSteele,

I get the following error "You've entered too many arguments for this function"

 

[joebean4jp]

jtakw's formula seemed to work. Thank you!

 

[jtakw]

You're welcome, welcome to the forum, and thanks for the feedback.