Format Cells Based on Given Input

[Rajesh Kr Joshi]

Hi,

I have 2 sheets in a excel file, in sheet1 I have start date and end date of a given task and in sheet2, I have dates from C6 to EY6 (1 Aug to 31dec) and in column B the task name. I need help in writing macro to merge the cells in sheet1 basis the date range and task from sheet2. If any expert can help me with his/her email I can send the sample file. Also is it possible to exclude holidays and weekends? Attaching the file.


Thanks
Rajesh83

[S M C]

Could you attach a sample of the expected output? How do you want the cells to be merged? Yes, weekends could be excluded, but you need to tell how you want the output to look like, given a sample of the input also

[S M C]

Ah, solution already suggested by Admin :)

cheers

[Admin]

Hi Rajesh,

Welcome to ExcelFox !!

It can be done through conditional formatting.

Define ranges:

EndDate

Refers to: =Sheet1!$G$4:$G$8

Holidays

Refers to: =Sheet1!$N$4:$N$23

SalesPersons

Refers to: =Sheet1!$C$4:$C$8

StartDate

Refers to: =Sheet1!$F$4:$F$8


Select C4:EY9 on sheet2

In Conditional formatting

Rule1:

=OR(WEEKDAY(C$3,2)>5,ISNUMBER(MATCH(C$3,Holidays,0)))

Rule2:


=AND(ISNUMBER(MATCH($B4,SalesPersons,0)),C$3>=INDEX(StartDate,MATCH($B4,SalesPersons,0)),C$3<=INDEX(EndDate,MATCH($B4,SalesPersons,0)),WEEKDAY( C$3,2)<6,ISNA(MATCH(C$3,Holidays,0)))


Format the cells accordingly.

Note: I have define a range for Holidays.

PFA.

If you prefer VBA rather than CF, let me know.

[Rajesh Kr Joshi]

Great, Thanks, the only question is how to delete the data (dots) from highlighted cells....actually each cell is refering 1 day and i wants to calculate the number of days worked by the sales man , by this way i can calculate the weekly utilization. For example to calculate the number of day worked in aug 1st week I am planing to use COUNTIF(C4:I4,"<>.") formula , this will count the blank cells which will be the number of days for me. If you can suggest a better way to do this...that will be a great great help. The final output i want the number os days woked by a sales man in a week.

Thanks
Rajesh

[Excel Fox]

Here's one way to do it

=IFERROR(SUMPRODUCT((C$3:I$3>=INDEX(Sheet1!$F$4:$F$8,MATCH($B12,SalesPersons,0) ))*(C$3:I$3<=INDEX(Sheet1!$G$4:$G$8,MATCH($B12,SalesPersons,0) ))*(WEEKDAY(C$3:I$3,2)<6)*ISNA(MATCH(C$3:I$3,Holidays,0))),0)

[Rajesh Kr Joshi]

WOW...this is awesome.....it is work for me...Let me implement this and surely share the feedback with you :).

Thanks a ton
Rajesh

[Rajesh Kr Joshi]

8889

Hi, I tried this and it worked great for the cell highlighting portion..but i was failed to implement the formula to calculate the days :(... that may be because i was trying to refelect the days in a diffrent sheet. I am attaching both the file i worked on. The Utilization file is the one i wants to refelect the days and sample allocation is the plotter which is working fine....also is there s way that if some one works on sat or sunday that can also be refelect in the plotter.

Thanks in anticipation.
Rajesh

[Excel Fox]

The utilization file does not have the start date and end date for each member

[Excel Fox]

Use this in D7 of utilization sheet

=SUMPRODUCT(('[Sample Allocation.xlsx]Plotter'!$K$4:$Q$4>=INDEX('[Sample Allocation.xlsx]Plan'!$H$4:$H$9,MATCH($B7,'[Sample Allocation.xlsx]Plan'!$G$4:$G$9,0)))*('[Sample Allocation.xlsx]Plotter'!$K$4:$Q$4<=INDEX('[Sample Allocation.xlsx]Plan'!$I$4:$I$9,MATCH($B7,'[Sample Allocation.xlsx]Plan'!$G$4:$G$9,0)))*ISNA(MATCH('[Sample Allocation.xlsx]Plotter'!$K$4:$Q$4,'[Sample Allocation.xlsx]Plan'!$R$3:$R$4,0)))

[Rajesh Kr Joshi]

Thanks , This worked for me.