Highlight the dates from given month and past years

[pipsmultan]

Greeting too all...


i am trying to apply Conditional formatting on the dates of joining from given month and year in "S6 & X6" , Which i am not successful to do so again

the 1st part is working fine to Highlight the dates which starts from 1st to 14th of every current month date and from 15th to 31th of previous month datw, with help of (Glenn Kennedy whom i am very grateful)

BUT i need minor updation..

i want to Highlight the month date from the given Month from given cell and previous years (i.e 2014, 2013,2012,2010.....). cant not figure out the solution

any help will be highly appreciated....

plz find the attachment

joining Date Hihghlight 2.xlsx

High Regards

[etaf]

select D:D
and apply a conditional formatting formula and use

=MONTH(D1)=MONTH(DATEVALUE("1"&$S$6&"2015"))

[bebo021999]

Not sure what you want, but it might helps to build your own formula:

With S6:month (mmmm), X6:year

This returns day 14th of same month of previous year: =DATEVALUE("14/"&$S$6&"/"&$X$6-1)
day 15th of previous month, previous year: =EDATE(DATEVALUE("14/"&$S$6&"/"&$X$6-1),-1)+1

[pipsmultan]

as an Reference

http://www.excelforum.com/excel-form...ml#post4229157

[pipsmultan]

[QUOTE=This returns day 14th of same month of previous year: =DATEVALUE("14/"&$S$6&"/"&$X$6-1)
day 15th of previous month, previous year: =EDATE(DATEVALUE("14/"&$S$6&"/"&$X$6-1),-1)+1[/QUOTE]

The above formula works with out putting it inside the "conditional formatting"... can you guide how to use it inside the Conditional formatting

any help will be appreciated

Regards

[pipsmultan]

Thanks for your Helpful reply appreciated. but unfortunately it don't solve my problem .. might be i am unable to explain my problem clearly..

i am trying to apply formula on "Conditional formatting" on the dates of joining to highlight the those date which has completed 1 year cycle for Salary increment. And our company follow some rules for salary increment.

Our Salary increment dates which starts from (1st to 14th) of its given month and (15th to 31th) its previous month, i.e If we are playing salary of the October month then those employees salary increment will be added who were appointed on "1st to 14th" October and "15th to 31th" its previous month (September) and this same will continue with the next coming month.

The problem i am facing is with the Conditional formatting formula which i m applying to picks the month and year from my given cell in sheet NOT from my system (computer)

=AND(DATE(YEAR(TODAY()),MONTH(C12),DAY(C12))>=DATE(YEAR(TODAY()),MONTH(TODAY())-1,15),DATE(YEAR(TODAY()),MONTH(C12),DAY(C12))<=DATE(YEAR(TODAY()),MONTH(TODAY()),14))

i have tried a lot on my own but cant figure it out

any help will be highly appreciated

Best Regards

[bebo021999]

Apply this for CF of D12:

=AND(DATE($X$6,MONTH(D12),DAY(D12))>=DATEVALUE("15/"&$S$6&"/"&$X$6),
DATE($X$6,MONTH(D12),DAY(D12))<=EDATE(DATEVALUE("14/"&$S$6&"/"&$X$6),1))

I tried to convert date in D12 to be same day, same month in year of 2015 (cell X6), then compare it with 15th Oct and 14th Nov of 2015

[pipsmultan]

Apply this for CF of D12:

=AND(DATE($X$6,MONTH(D12),DAY(D12))>=DATEVALUE("15/"&$S$6&"/"&$X$6),
DATE($X$6,MONTH(D12),DAY(D12))<=EDATE(DATEVALUE("14/"&$S$6&"/"&$X$6),1))

I tried to convert date in D12 to be same day, same month in year of 2015 (cell X6), then compare it with 15th Oct and 14th Nov of 2015

Many Many thanks for your Reply and help. there is some issue in Month which need to change

When i enter Month October: It shows result "1st to 14th" November and "15th to 31th" its previous month (October)

I need to accomplish When i enter Month October: It showing "1st to 14th" October and "15th to 31th" its previous month (September)

Regards

[pipsmultan]

any help and solution will be highly appreciated

[samba_ravi]

Select Column D and use below formula in conditional format

=TEXT(EOMONTH($D12,(DAY($D12)>14)+0),"MMMM")=$S$6

[pipsmultan]

Select Column D and use below formula in conditional format

=TEXT(EOMONTH($D12,(DAY($D12)>14)+0),"MMMM")=$S$6

Working great

We will always be grateful for all that you have done to help us (nflsales). And also to all those who reply and tried to Solve highly appreciated

Reputation has been Added and Thread is marked as Solved

Best Regards

[kvsrinivasamurthy]

Formula for CF for D12

=AND(D12>=DATE($X$6,MONTH($S$6)-1,15),D12<=DATE($X$6,MONTH($S$6),14))

Copy to other cells.
Instead of entering month name in C6 , enter date and format the cell as Custom-->mmmm.Pl see file.

[samba_ravi]

Thanks for your feedback and adding reputation