Hello! I'm new here and I could use some help with some functions.
I have a database and want to define a criteria range (to use it in hypothesis testing), and one of them is that the date should be in the last decade of the previous month.
How do I get this?
Thanks!
Hi and welcome to the forum.
Try
=DATE(YEAR(A2)-10;MONTH(A2)-1;DAY(A2))
Change the semi-colons to comma, if you have to do this.
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
=EDATE(A1,-121)
if the date in cell A1 is May 09, 2012, this formula will give you April 09, 2002. is that what you are looking for? the number 121 corresponds to 12 months in 10 years plus one more month. the minus sign is for back-dating; drop it and you get forward-dating.
- i.s.z -
CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
All good ideas are courtesy resources from this forum as well as others around the web.
- e.o.m -
Sorry if I haven't expressed myself clearly. When I said the last decade of the previous month, I meant the last 10 days (approximately) of the previous month. Since it's May now, the date should be between April 20th and April 30th :D
!!! decade means 10 years!!!
if you can do with the last day of April, then this will work for you:
=EOMONTH(A1,-1)
if you want a date that is somewhere between 20th and 30th (excluded), then you can go ahead and deduct 7 days (just an example) from the above:
=EOMONTH(A1,-1) - 7
remember to "date" format the cell in which this formula is placed.
Last edited by icestationzbra; 05-09-2012 at 03:38 AM.
Now...You confused me. ..
Thanks, I'll try this one.
And I swear I know decade referrs to years, but the stupid exercise was written this way
oh, so getting help doing homework, eh???
if so, it is unfair to your peers...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks