cancel
Showing results for
Did you mean:
Helper I

## Calculate number of wees between 2 dates. If a date is blank use 1/1/2017

Hello all,

I need help calculating the number of weeks between 2 columns.  The challenge is one of the columns may be blank at times.  If they are blank i'd like to the formula to use another date automatically.  For the purposes pf this example I have the following columns:

 Start Date End Date # of weeks (need help calculating) 1/1/2017 5/5/2017 2/2/2017 5/5/2017 6/20/2017 5/5/2017 4/15/2017 5/5/2017 5/5/2017 1/1/2017 5/5/2017 1/1/2017 5/5/2017 1/1/2017 5/5/2017

- If the start date is missing, I'd like to use the beginning of the year (1/1/2017) as the start date to calculate the number of weeks.

- If the end date is missing, I'd like to use the current date (today) for the end date.

- If both dates are present, I'd like to use those dates as is.

- I will be summing the number of weeks to across different regions and groups to show the total number of weeks active.

I'm not sure if this is a measurement or if I need to add a column.  I'll be using this rolled up at different levels of  a hierarchy and need to calculations to adjust based on the what the user is filtering by.

Any help on this is greatly appreciated.

ScORE

1 ACCEPTED SOLUTION
Microsoft

@ScORE wrote:

Hello all,

I need help calculating the number of weeks between 2 columns.  The challenge is one of the columns may be blank at times.  If they are blank i'd like to the formula to use another date automatically.  For the purposes pf this example I have the following columns:

 Start Date End Date # of weeks (need help calculating) 1/1/2017 5/5/2017 2/2/2017 5/5/2017 6/20/2017 5/5/2017 4/15/2017 5/5/2017 5/5/2017 1/1/2017 5/5/2017 1/1/2017 5/5/2017 1/1/2017 5/5/2017

- If the start date is missing, I'd like to use the beginning of the year (1/1/2017) as the start date to calculate the number of weeks.

- If the end date is missing, I'd like to use the current date (today) for the end date.

- If both dates are present, I'd like to use those dates as is.

- I will be summing the number of weeks to across different regions and groups to show the total number of weeks active.

I'm not sure if this is a measurement or if I need to add a column.  I'll be using this rolled up at different levels of  a hierarchy and need to calculations to adjust based on the what the user is filtering by.

Any help on this is greatly appreciated.

ScORE

@ScORE

Just create a calculated column as

```weeks =
DIVIDE (
IF ( ISBLANK ( yourTable[End Date] ), TODAY (), yourTable[End Date] )
- IF (
ISBLANK ( yourTable[Start Date] ),
DATE ( 2017, 1, 1 ),
yourTable[Start Date]
),
7
)
```

Microsoft

@ScORE wrote:

Hello all,

I need help calculating the number of weeks between 2 columns.  The challenge is one of the columns may be blank at times.  If they are blank i'd like to the formula to use another date automatically.  For the purposes pf this example I have the following columns:

 Start Date End Date # of weeks (need help calculating) 1/1/2017 5/5/2017 2/2/2017 5/5/2017 6/20/2017 5/5/2017 4/15/2017 5/5/2017 5/5/2017 1/1/2017 5/5/2017 1/1/2017 5/5/2017 1/1/2017 5/5/2017

- If the start date is missing, I'd like to use the beginning of the year (1/1/2017) as the start date to calculate the number of weeks.

- If the end date is missing, I'd like to use the current date (today) for the end date.

- If both dates are present, I'd like to use those dates as is.

- I will be summing the number of weeks to across different regions and groups to show the total number of weeks active.

I'm not sure if this is a measurement or if I need to add a column.  I'll be using this rolled up at different levels of  a hierarchy and need to calculations to adjust based on the what the user is filtering by.

Any help on this is greatly appreciated.

ScORE

@ScORE

Just create a calculated column as

```weeks =
DIVIDE (
IF ( ISBLANK ( yourTable[End Date] ), TODAY (), yourTable[End Date] )
- IF (
ISBLANK ( yourTable[Start Date] ),
DATE ( 2017, 1, 1 ),
yourTable[Start Date]
),
7
)
```