Week to date converter

We enjoy looking at time-spans both in weeks and in years plus days. Or try one of our other investment or economics tools. Please enter all required fields. Correct invalid entries. Don't Quit Your Day Job DQYDJ may be compensated by our partners if you make purchases through links.

The output should resemble this:. Now that we have the helper columns in place, we can assemble a SUMIFS function to add up the tasks achieved for each week. To see if the year matches the current row, we need to set the criteria to be the YEAR of the date in cell A2. That takes care of the tally of the Achieved Tasks , but we still need to calculate the number of total Tasks for each calculation.

We need to combine the statements, dividing the total Achieved Tasks by the total Tasks to get the ratio we need for the percentage. The final formula in cell H2 is as follows:. Drag down the formula to populate the rest of the cells in column H.

The output should look like this:. Select the labels for columns E and F. Right click the selection and choose Hide. Month-To-Date starts at the beginning of the month and adds up all the rows that occur in the same month of the same year, up until the current day.

In order to calculate a Month-To-Date column, we need to be able to compare the month that each row occurs in and the day of the month for each row. DAY calculates the day of the month. The syntax for the new functions is very straightforward:.

In cell I2 create a formula to calculate the month of the date in A2. In cell J2 create a formula that calculates the week number of the date in A2.

Drag down the formulas to populate the rest of the cells in columns I:J. The output will look like this:. Now that we have the helper columns in place, we can assemble a SUMIFS function to add up the tasks achieved for each month. The process is the same as for Week-To-Date. To see if the day is equal to or less than the current row, we need to set the criteria to be the DAY of the date in cell A2.

The final formula in cell K2 is as follows:. Select the labels for columns I and J. Year-To-Date starts at the beginning of the year and adds up all the rows that occur in the same year, up until the current day. Because in this example; we are going to use DimDate as our date table, select this table in the list of fields in report tab, or data tab , and mark it as date table.

We already have the day number of week in the DimDate provided by AdventureWorks. However, we calculate it again, just in case you use the built-in date table. Using LastDate function we get the date value of the current filter context, and wrap it inside a WeekDay function to fetch the day number of week.

The result of this measure would be the day number of week starting from Sunday as 1, ending Saturday as 7. Not always in all businesses the week starts from Sunday.

In fact, in many businesses, week starts on Monday. WeekDay function has a second parameter which can determine the starting day of week. If you change it to 3; Monday will be 0, and Sunday 6. This one will appeal more for the rest of our calculation. Set this parameter to 3. Now that we know the day of week, it is easy to calculate start of the week. You need to go that number back as days interval.

For example; Wednesday is day 2 of the week. Using DateAdd function you can go as many days back you want. Here is the result. Now that we have the start of week, we can calculate all dates between that date and the date of the current filter context using DatesBetween, and wrap it inside a calculate to calculate Week to Date.