date formula

B

brianwa

I have a spreadsheet that has the days of the month down column A. I'
looking for a formula say in cell B1 that does the following, if today
date < the first day of the month then return the first day of th
month, then if today's date > the last day of the month then return th
last day of the month.

Thanks in advance
B
 
F

Frank Kabel

Hi
maybe I#m missing something but how can todays date be smaller than the
first day of a month (it is at least the first day in a month)?
 
B

brianwa

My bad,

I'm currently looking at data for march 04. So as of today I would lik
it to return march 1st. After march 31st I would like it to show marc
31. In between the 1st and 31st it should show today()-1.

Does this make sense?

B
 
F

Frank Kabel

Hi
o.k. if your base date filed (e.g. your march date) is in cell A1 try
the following (not fully tested)
=IF(OR(MONTH(TODAY())<MONTH(A1),YEAR(TODAY())<YEAR(A1)),DATE(YEAR(A1),M
ONTH(A1),1),IF(OR(MONTH(TODAY())>MONTH(A1),YEAR(TODAY())>YEAR(A1)),DATE
(YEAR(A1),MONTH(A1)+1,1)-1,TODAY()))
 
B

Bob Phillips

Still doesn't make sense.

You say as of today it should show March 1st, but in between 1-31st it
would show TODAY()-1. Now is between 1-31.

Best I can do is

=DATE(YEAR(TODAY()),MONTH(TODAY()),IF(YEAR(TODAY()-1)&MONTH(TODAY()-1)<YEAR(
TODAY())&MONTH(TODAY()),1,IF(YEAR(TODAY()+1)&MONTH(TODAY()+1)>YEAR(TODAY())&
MONTH(TODAY()),DAY(TODAY()),DAY(TODAY()-1))))

--

HTH

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

Frank Kabel

Hi
what error message did you receive (you may have to remove the
linebreaks in your formula. This is only ONE line). Formula works for
me
 
B

brianwa

I think I got it

=IF(TODAY()<B10,B10,IF(TODAY()>B40,B40,TODAY()-1))

B10 & B40 contain the first and last day of march.

B
 
Top