Report 1st of the month

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi al
I have a spreadsheet that works on Week Commencing Sunday dates (one per column for 52 weeks)

To show the Month above the week commencing dates I simply took the first week commencing date (cell A7) and formatted it as MMM-YY, (ie in cell A5 I entered "=A7") for following months I used the formula "=A5+31".

The problem is that the first Week Commencing date is rarely the actual 1st of the month and this causes problems with other formula's that refer to the month cells

In the Month cell (A5) what is the formula I need that will take whatever W/C Sunday date in A7 (which is always the first Sunday of the month) and report the 1st of whatever that month is

Any help is always appreciate
BeSmart
 
=DATE(YEAR(A7),MONTH(A7),1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

BeSmart said:
Hi all
I have a spreadsheet that works on Week Commencing Sunday dates (one per column for 52 weeks).

To show the Month above the week commencing dates I simply took the first
Click to expand...
week commencing date (cell A7) and formatted it as MMM-YY, (ie in cell A5 I
entered "=A7") for following months I used the formula "=A5+31".
The problem is that the first Week Commencing date is rarely the actual
Click to expand...
1st of the month and this causes problems with other formula's that refer to
the month cells.
In the Month cell (A5) what is the formula I need that will take whatever
Click to expand...
W/C Sunday date in A7 (which is always the first Sunday of the month) and
report the 1st of whatever that month is?
 
Hi all
I have a spreadsheet that works on Week Commencing Sunday dates (one per column for 52 weeks).

To show the Month above the week commencing dates I simply took the first week commencing date (cell A7) and formatted it as MMM-YY, (ie in cell A5 I entered "=A7") for following months I used the formula "=A5+31".

The problem is that the first Week Commencing date is rarely the actual 1st of the month and this causes problems with other formula's that refer to the month cells.

In the Month cell (A5) what is the formula I need that will take whatever W/C Sunday date in A7 (which is always the first Sunday of the month) and report the 1st of whatever that month is?

Any help is always appreciated
BeSmart
Click to expand...


=A7+1-DAY(A7)


--ron
 
Back
Top