Excel formula to calculate the no of weeks from a fix date
- Thread starter jrl1208
- Start date
NARAYANK991
Excel Ninja
Hi ,
If you are not particular that you want a particular day of the week , but just the same day of the week as the start date , but the given number of weeks from that date , then you can try :
=Start_Date + Given_number_of_Weeks * 7
where Start_Date = 30/06/2014 , and Given_number_of_Weeks = 52
Narayan
If you are not particular that you want a particular day of the week , but just the same day of the week as the start date , but the given number of weeks from that date , then you can try :
=Start_Date + Given_number_of_Weeks * 7
where Start_Date = 30/06/2014 , and Given_number_of_Weeks = 52
Narayan
Hi Narayan
Thanks for your responds. However I tried the formula but it's not giving me the answer I want
=30/06/2014+(52*7) = 364.002
I actually want to 52 weeks Prior to 30/06/2014 will return what date.
For Example Current date is 02/05/2014, deduct 2 weeks, answer is 18/04/2014
Thanks for your responds. However I tried the formula but it's not giving me the answer I want
=30/06/2014+(52*7) = 364.002
I actually want to 52 weeks Prior to 30/06/2014 will return what date.
For Example Current date is 02/05/2014, deduct 2 weeks, answer is 18/04/2014
Somendra Misra
Excel Ninja
Hi jrl1208
You should enter a date in a cell, no. of weeks in a different cell with +ve for adding and -ve for subtracting.
Than use those ref. in formula.
Say in A1 02/05/2014
in B1 -2
in C1 put formula =A1+(B1*7)
Regards,
You should enter a date in a cell, no. of weeks in a different cell with +ve for adding and -ve for subtracting.
Than use those ref. in formula.
Say in A1 02/05/2014
in B1 -2
in C1 put formula =A1+(B1*7)
Regards,
NARAYANK991
Excel Ninja
Hi ,
The problem is that Excel recognizes data entered as dates based on whether it is a valid date in your system date format , and whether it is a standalone item of data.
Thus , if your system date format is dd/mm/yyyy , then Excel will recognize 24/05/2014 as a valid date , but will interpret 05/24/2014 as text.
Also , 24/05/2014 will be recognized as a valid date , but 24/05/2014 - (2*7) will become text.
Lastly , if you try to use the = sign to tell Excel to interpret the entire expression as a formula , then Excel takes the expression 24/05/2014 not as a valid date , but as 24 divided by 5 divided by 2014 !
So either you go with what Misra has posted , or you have to put the whole expression in a format that Excel can understand and evaluate ; so you can enter :
=DATEVALUE("24/05/2014") - (2*7)
and you should have your answer.
Narayan
The problem is that Excel recognizes data entered as dates based on whether it is a valid date in your system date format , and whether it is a standalone item of data.
Thus , if your system date format is dd/mm/yyyy , then Excel will recognize 24/05/2014 as a valid date , but will interpret 05/24/2014 as text.
Also , 24/05/2014 will be recognized as a valid date , but 24/05/2014 - (2*7) will become text.
Lastly , if you try to use the = sign to tell Excel to interpret the entire expression as a formula , then Excel takes the expression 24/05/2014 not as a valid date , but as 24 divided by 5 divided by 2014 !
So either you go with what Misra has posted , or you have to put the whole expression in a format that Excel can understand and evaluate ; so you can enter :
=DATEVALUE("24/05/2014") - (2*7)
and you should have your answer.
Narayan
vijay.vizzu
Member
You can also try =DATE(2014,6,30)+(52*7) to get your desired output in a cell