• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Date Calculation [SOLVED]

P

Pofski

Member
Hi everybody,


i seem to have got stuck on a date calculation.


ok, so i have 1 column with date, and another column with a recurrence indicator in the form of for example 6 M (6 months) or 80 D (80 days) or 7 W (7 weeks).

These numbers varies.


Now, what i need to get is a column with the recurrences that are still left this year.

So in other words, if you have for example 1/1/2013 and a recurrence of 1 M, there are still 11 recurrences left (first of each month).


On top of that the amount of occurrences that should already have been done this year.


Thanks in advance.


Sincerely,


Pofski
 
If my data is in A2:D5 like:

[pre]
Code: 
Date		Period	Remaining	Completed
25/11/2013	80 d	 0.45 	 	4.10
17/06/2013	2 w	 14.07 	 	11.93
1/01/2013	1 m	 12.07 	 	0
[/pre]
I'd use something like:


Remaining periods:

=IF(RIGHT(B3,1)="d",(DATE(YEAR(A3),12,31)-A3)/LEFT(B3,FIND(" ",B3)),IF(RIGHT(B3,1)="w",(DATE(YEAR(A3),12,31)-A3)/(7*LEFT(B3,FIND(" ",B3))),IF(RIGHT(B3,1)="m",(DATE(YEAR(A3),12,31)-A3)/(30.25*LEFT(B3,FIND(" ",B3))),"")))


Completed periods:

=IF(RIGHT(B3,1)="d",(A3-DATE(YEAR(A3),1,1))/LEFT(B3,FIND(" ",B3)),IF(RIGHT(B3,1)="w",(A3-DATE(YEAR(A3),1,1))/(7*LEFT(B3,FIND(" ",B3))),IF(RIGHT(B3,1)="m",(A3-DATE(YEAR(A3),1,1))/(30.25*LEFT(B3,FIND(" ",B3))),"")))


or as an example: https://www.dropbox.com/s/ffteoqoul74kyd4/Pofski.xlsx
 
You must log in or register to reply here.
Back
Top