CODEWITHSUNDEEP
excel
sali
sali

Reputation: 13

Find last digits with IF

I have lots of cells in the spreadsheet that contains the whole year, date and stock prices, but I only need the dates from 25th of the last month till the 5th of this month.How can I write a function to do that? For example if the cells are like this in the column: 

19900101  
19900102 
19900103
19900104
19900105 
.  
.  
19900125
19900126  
19900127  
19900128
19900129
19900130
19900131

I want it to show only days (1,2,3,4,5,25,26,27,28,29,30,31) with the date format. The date shows in the text format and I've tried format cell to change it but it doesn't work.

Upvotes: 0

Views: 255

Answers (3)

barry houdini
barry houdini

Reputation: 46371

For a slightly different approach, assuming you want to return the A2 value only if it represents a date in the last 5 or first 5 days of any month you can use this formula in B2

=IF(DAY(TEXT(A2,"0000-00-00")+5)<11,A2,"")

TEXT function converts your data to a real date then if you add 5 days to that date then any date that was originally in the last 5 or first 5 of the month will now be in the first 10 days of the month.....so it's sufficient to check that the day is < 11. That will pick up 27th to 31st of a 31 day month, 26th to 30th of a 30 day month etc.

Upvotes: 4

Andrew
Andrew

Reputation: 7880

Based on all the edits and comments, these should be the function you need.

=IF(OR(A7-A2>5,MOD(A2,100)<=5),DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2)),"")

You should place it in cell B2 and then do all the desired filtering, as explained in xQbert's answer.

Upvotes: 0

xQbert
xQbert

Reputation: 35333

you could use a formula and then filter by rows with data... As an example (given it's difficult to discern if you always want the first 5 and last 5 of a month or what....)

=IF(RIGHT(A2,2)*1<=2,A2,IF(RIGHT(A2,2)*1>=29,A2,""))

enter image description here

Upvotes: 0

Related Questions