Week wise date calculation formula

[kishoremcp]

I have an excel sheet where in cell D1 there will be a start date.

Now what I want is,
in Column A there it is mentioned as First Week, and in corresponding cell in column B there is Day 4.
so I need a formula which should give the output date in cell G6 as (if first week+day 4) then it should take the first week first day as the start date and calculate the day 4,

Example( start date is 12th October) then in G6 it should be 12+day4(4) = Oct 16.
similarly in G7 also Oct 16, G8 should be oct 20(second week=7+day 1=1: Total 7+1=8) so oct 12+8= oct 20.

Week should take the statr date as the week 1 day 1 (i think )

like that i need in the remaining cells also but with a formula.

Can any one help..I hope i am clear with examples.

Thanks in advance and attached the file for reference..

[zbor]

Here, try this.
You must have define names (for weeks and days in M and N column) in ascending order.

Also, in First week in A6 and A7 remove space at the end.

[Sindhus]

Hi, You can try this formula in G6

Please Login or Register  to view this content.

In order to identify first as 1, second as 2, etc. I have added the details in column J:K and used the lookup option in that column

J1: First K1: 1
J2: Second K2:2
etc.

Regards,
Sindhu

[kishoremcp]

Okay. Good. But i have one doubt. If i enter "oct 1" in start date then week 4 day 6 is oct 28. What if I want the start date as "oct 1" and the out put date as oct 31. how is it possible? Because I want to implement an update to run on oct 31 which the output date column willnot have at any time with start date as oct 1. Any idea please...

[Sindhus]

I couldn't understand. Could you elaborate more?

[kishoremcp]

Sindhus, first of all thank you for helping me...
One more thing is, I could not understand that vlookup or something as u said. However for the above scenario, some months have five weeks. So if i have to mention only four weeks then how is it possible if i enter oct 1 the max date which i get is for (week four-day six is oct 28) what about the rest of the three days. so, please help me and please upload the attachment as well because i didnot understnd the vlookuop concept which you said.

Thanks in advance

[Sindhus]

You can enter Fifth and 5 in J and K column. Check the attached sheet

[kishoremcp]

Thank you for your help. My Question is is there any chance that we can do it without Week 5. Only with four weeks

[Sindhus]

Then the no. of days should be higher. If OCt 1st is the start of first week, then fourth week starts on 22nd oct. So, there are 9 more days to reach 31st oct. So, day should be Day 9

[kishoremcp]

Actually i know that it can be done with Day 9 as per your logic. But there can be a validation with month with which it can be done without that also i think..

[Sindhus]

Ok now i got your point. So, then if start of first week is 1st oct 2012. Then what is fourth week - day 6? 26th oct 2012?

[kishoremcp]

Yes, that is my problem

[Sindhus]

Then try this formula in c column

Please Login or Register  to view this content.

[kishoremcp]

Hi Sindhus,

Thank you again, but first week of day 4 should be oct 5 because (1, 4=1+4=5) which is oct 5. But ur formula is giving oct 10 as the output.

[Sindhus]

Oops! it should be

Please Login or Register  to view this content.

[kishoremcp]

One last help please, the above formula is working good. but if i enter oct 1 as the start date then the first week of first day is coming as oct 2. But i need it as oct1.
I need to know where to modify the formula.
Please help...

[kishoremcp]

If i have week 1, 2,3,4 and day from 1 to 7 any help please because if a month has 5 weeks i am having a problem...please

[Sindhus]

I have modified the formula for the problem in first week days. I dont understand the problem in fifth week.

Please Login or Register  to view this content.