How to Calculate Week Number and total work done within a week

kanishk Chaturvedi

I am trying to calculate total work done within a week for a job and previous week as well.

StevenBlackburn

Hi Kanishk,

Without knowing the structure of your sheet and how you have structured the backend to manage the numbers — I can say that you would want to probably start with a SUMIFS formula. The SUMIFS formula basically says that you want to calculate the hours column if the date that the hour is being tracked is today or in the past up to 6 days for the calculation of hours worked for the current week.

For the previous week, you would simply have the same formula concept but with some minor changes; the date range of the hours calculation would need to occur on the 7th day to the 13th.

This would allow for a side by side comparison of current week to previous week. Your formulas could look something like the below:

Current Week:

=SUMIFS([Hours Column]:[Hours Column], [Date Column]:[Date Column], <= TODAY(), [Date Column]:[Date Column], >= TODAY() - 6)

Previous Week:

=SUMIFS(
[Hours Column]:[Hours Column],
[Date Column]:[Date Column], <= TODAY() - 7,
[Date Column]:[Date Column], >= TODAY() - 13
)

Please let us know if this helped you!