date formula

  • Thread starter Thread starter brianwa
  • Start date Start date
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
 
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)?
 
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
 
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()))
 
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)
 
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
 
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
 
Back
Top