calculate numbers of days elapsed a week, month or 6 months from now

Using your posted workbook...
Note:
No calculation will be "accurate" if you don't have a start date and a duration.
I set a standard month at 1/12 of a year and a week as 1/365 of a year.

Try this regular formula, copied down

Please Login or Register  to view this content.

Is that something you can work with?

What about using a start date of today(). How would I do it then?

DCF is a daycount calculation. So I actually need a number which is frac/365 days per year.

If the start date is today, try this regular formula, copied down

Please Login or Register  to view this content.

Is that something you can work with?

Regarding: LOOKUP(10^10,--LEFT(A2,{1,2}))*7

This section: LEFT(A2,{1,2})
Returns a 2-element array that contains the left one and two characters of A2
If A2 contains 1W...the array is: {"1","1w"}

The double-minus is an unambiguous way to coerce numeric text into an actual number.
So --{"1","1w"} becomes {1,#VALUE!}

If A2 contained 10M, the final array would be {1,10}

(You could also multiply by 1, divide by 1, etc...but those methods might be misinterpreted as formula typos.)

Last, the LOOKUP function has a convenient feature. If the lookup value is larger than any of the list elements, it returns the LAST element of the search type (text or numeric). If the lookup value is numeric, it returns the last numeric element. If the lookup value is text, it returns the last text element.

Since we're looking for a number and that number is certain to be less than 10^10, the above formula returns the largest number in the array.

I hope that helps.

(EDATE(TODAY(),LOOKUP(10^10,--LEFT(A2,{1,2})))-TODAY())

this returns a date format which can't be divided by 365. how come it's working in this formukla?>

That formula section does not return a date format. It returns the number of days between today and the date that is x-number-of-months in the future as specified in A2. If your test cell displays a date, that's only because it's formatted as a date. You should be able to enter this: =(EDATE(TODAY(),LOOKUP(10^10,--LEFT(A2,{1,2})))-TODAY())/365 without incident