chart will not pick monday data

[MohitMathur1234]

Hi there,

I have some data and i want to show data weekday wise. but i am stuck somewhere
range is
Weekdays 0 4 5 0 4
Mon Tue Wed Thu Fri
If I talk about Dec month and select week 1 of dec, but there is no monday in week 1 in dec month so chart will not pick monday data boz there is no monday in dec's week 1.

please help me out of this.

 

[Khalid NGO]

Hi Mohit,

Can you post an example of your file with expected output?

Regards,

 

[MohitMathur1234]

Hi
please find the attachment.

 

[Khalid NGO]

Hi Mohit, sorry for the delay, busy today.

I am not sure about your logic of weekdays, 1st week does not always starts with Monday; well I have made a table for dates, weekdays, and weeknumbers, see if it helps.

 

[MohitMathur1234]

Hi Khalid,

Please find attachment as i have explain more with base data.

 

[Khalid NGO]

Dear Mohit,
Again I am not sure...

The formula you have used in B14:
=CHOOSE(WEEKDAY(C9),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")

WEEKDAY is not necessary here, as you are not using the real dates, result will be same with:
=CHOOSE(C9,"Sun","Mon","Tue","Wed","Thu","Fri","Sat")

Since you are not using real dates, how would excel realize that the C9 (Monday 2nd Nov 2015) is not exist in first week.

For example, your cell:
E5 = 2015
H5 = November

and C9 = 2

So the 2-Nov-2015 = Monday (that is okay)
but if you change H5 from November to (say) December
then 2-Dec-2015 is not Monday, but it is Wednesday. Your above formula still shows the Monday.

Therefore I did used real dates in my first attachment.

If above explanation does not clarifies, I will assume that I have completely misunderstood and I am still in the dark; and in that case you will need to wait someone for suitable answer.

Regards,

 

[XLPadawan]

Mohit,
I took a different approach that is working for me. You can change the month # via a drop-down. Look at a calendar for October 2015, then change the month # to 10 and check your results.