formula: First and last day in month
 

Hi,

I have sheet:
In A column exist date
In B column exist "x" or null
In C column please bulid formula: first or last day in month only for day if
in column B exist null.

Example:

A B Formula in C kolumn
5/1/05 x
5/2/05 x
5/3/05 first day of month
....
5/28/05 last day of month
5/29/05 x
5/30/05 x
5/31/05 x
6/1/05 first day of month
6/2/05 x
.....
6/29/05 last day of month
6/30/05 x
7/1/05 first day of month

Thanks
Zbych

Hi!

Enter this formula in C1 using the key combo of CTRL,SHIFT,ENTER:

=IF(B1="X","",IF(A1=MIN(IF(B$1:B$13<"X",IF(MONTH( A$1:A$13)=MONTH(A1),IF(YEAR(A$1:A$13)=YEAR(A1),A$1 :A$13)))),"First
day",IF(A1=MAX(IF(B$1:B$13<"X",IF(MONTH(A$1:A$13) =MONTH(A1),IF(YEAR(A$1:A$13)=YEAR(A1),A$1:A$13)))) ,"Last
day","")))

That's a real beast! Maybe someone can come up with a cleaner solution. I
don't have the time.

Biff

"Zbigniew Lewandowski" wrote in message
...
Hi,

I have sheet:
In A column exist date
In B column exist "x" or null
In C column please bulid formula: first or last day in month only for day
if in column B exist null.

Example:

A B Formula in C kolumn
5/1/05 x
5/2/05 x
5/3/05 first day of month
...
5/28/05 last day of month
5/29/05 x
5/30/05 x
5/31/05 x
6/1/05 first day of month
6/2/05 x
....
6/29/05 last day of month
6/30/05 x
7/1/05 first day of month

Thanks
Zbych

=if(B2="",date(year(a2),month(a2),1))

is first day, but what determines whether to calculate first or last?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Zbigniew Lewandowski" wrote in message
...
Hi,

I have sheet:
In A column exist date
In B column exist "x" or null
In C column please bulid formula: first or last day in month only for day
if
in column B exist null.

Example:

A B Formula in C kolumn
5/1/05 x
5/2/05 x
5/3/05 first day of month
...
5/28/05 last day of month
5/29/05 x
5/30/05 x
5/31/05 x
6/1/05 first day of month
6/2/05 x
....
6/29/05 last day of month
6/30/05 x
7/1/05 first day of month

Thanks
Zbych

Hi

It looks as though if the date is before mid-month you want first day,
and after mid-month then the last day.
If this is so, then in C1
=IF(B1="","",IF(DAY(A1)<=15,DATE(YEAR(A1),MONTH(A1 ),1),DATE(YEAR(A1),MONTH(A1)+1,0)))

Clearly the approximation of 15 for mid-month is slightly out for February.

Regards

Roger Govier



Zbigniew Lewandowski wrote:

Hi,

I have sheet:
In A column exist date
In B column exist "x" or null
In C column please bulid formula: first or last day in month only for day if
in column B exist null.

Example:

A B Formula in C kolumn
5/1/05 x
5/2/05 x
5/3/05 first day of month
....
5/28/05 last day of month
5/29/05 x
5/30/05 x
5/31/05 x
6/1/05 first day of month
6/2/05 x
.....
6/29/05 last day of month
6/30/05 x
7/1/05 first day of month

Thanks
Zbych

first day: =IF(ISBLANK(B3),DATEVALUE(MONTH(A3)&"/01/"&YEAR(A3)),"")

last day: =IF(ISBLANK(B3),DATEVALUE(MONTH(A3)+1&"/01/"&YEAR(A3))-1,"")

But it's not clear, when you want first day and when last day!

Regards,
Stefi

€žZbigniew Lewandowski€� ezt Ã*rta:

Hi,

I have sheet:
In A column exist date
In B column exist "x" or null
In C column please bulid formula: first or last day in month only for day if
in column B exist null.

Example:

A B Formula in C kolumn
5/1/05 x
5/2/05 x
5/3/05 first day of month
....
5/28/05 last day of month
5/29/05 x
5/30/05 x
5/31/05 x
6/1/05 first day of month
6/2/05 x
.....
6/29/05 last day of month
6/30/05 x
7/1/05 first day of month

Thanks
Zbych

It worked perfectly, thanks so much for your help :)

Zbych

Hi!

Enter this formula in C1 using the key combo of CTRL,SHIFT,ENTER:


=IF(B1="X","",IF(A1=MIN(IF(B$1:B$13<"X",IF(MONTH( A$1:A$13)=MONTH(A1),IF(YEA
R(A$1:A$13)=YEAR(A1),A$1:A$13)))),"First

day",IF(A1=MAX(IF(B$1:B$13<"X",IF(MONTH(A$1:A$13) =MONTH(A1),IF(YEAR(A$1:A$1
3)=YEAR(A1),A$1:A$13)))),"Last
day","")))

That's a real beast! Maybe someone can come up with a cleaner solution. I
don't have the time.

Biff

"Zbigniew Lewandowski" wrote in message
...
Hi,

I have sheet:
In A column exist date
In B column exist "x" or null
In C column please bulid formula: first or last day in month only for
day
if in column B exist null.

Example:

A B Formula in C kolumn
5/1/05 x
5/2/05 x
5/3/05 first day of month
...
5/28/05 last day of month
5/29/05 x
5/30/05 x
5/31/05 x
6/1/05 first day of month
6/2/05 x
....
6/29/05 last day of month
6/30/05 x
7/1/05 first day of month

Thanks
Zbych

formula: First and last day in month
 

Your're welcome. Thanks for the feedback!

Biff

"Zbigniew Lewandowski" wrote in message
...
It worked perfectly, thanks so much for your help :)

Zbych

Hi!

Enter this formula in C1 using the key combo of CTRL,SHIFT,ENTER:


=IF(B1="X","",IF(A1=MIN(IF(B$1:B$13<"X",IF(MONTH( A$1:A$13)=MONTH(A1),IF(YEA
R(A$1:A$13)=YEAR(A1),A$1:A$13)))),"First

day",IF(A1=MAX(IF(B$1:B$13<"X",IF(MONTH(A$1:A$13) =MONTH(A1),IF(YEAR(A$1:A$1
3)=YEAR(A1),A$1:A$13)))),"Last
day","")))

That's a real beast! Maybe someone can come up with a cleaner solution. I
don't have the time.

Biff

"Zbigniew Lewandowski" wrote in message
...
Hi,

I have sheet:
In A column exist date
In B column exist "x" or null
In C column please bulid formula: first or last day in month only for
day
if in column B exist null.

Example:

A B Formula in C kolumn
5/1/05 x
5/2/05 x
5/3/05 first day of month
...
5/28/05 last day of month
5/29/05 x
5/30/05 x
5/31/05 x
6/1/05 first day of month
6/2/05 x
....
6/29/05 last day of month
6/30/05 x
7/1/05 first day of month

Thanks
Zbych