Compare for time and count in cells for days

[MohitParashar]

Hi Team, 

I need your help to prepare a data which will include today's date and compare the creation date for total number of days. After getting the total number of days I need to show them in a format where User A has total count is last 3 days, last 5 days, last 7 days, last 10 days and so on... At the end I need to show the total count in every column. A basic manual table  I have pasted below to give an overview of the look of the table will be require, could someone please help me to apply a formula and tell me how to done the job right?

Assigned_Group	<1 Days	<2 Days	>5 Days	> 7 Days	>15 Days	>30 Days	>60 Days	>90 Days	>180 Days	Grand Total
Mohit	1			3		6				10
Suresh		2	2							4
Deepak	--	--	--	--	--	--	--	--	1	1

#excel

[#2]

@MohitParashar 

You might help us to help you by showing what your data look like.

[#3]

@MohitParashar 

To create a table that calculates the total count of days for each assigned group based on the creation date, and then categorizes them into different time frames (e.g., last 3 days, last 5 days, etc.), you can follow these steps:

1. Calculate Total Days for Each Assigned Group:
• Use the TODAY() function to get today's date.
• Subtract the creation date from today's date to get the total number of days.
• Use conditional formulas to categorize the total days into different time frames.
2. Create a Table Structure:
• Create column headers for each time frame (e.g., <1 Days, <2 Days, etc.).
• Create rows for each assigned group.
• Calculate the total count for each time frame for each assigned group.

Here's how you can apply the formulas:

Assuming your data starts from cell A1 with headers "Assigned_Group" in column A and "Creation_Date" in column B, and the first row of data starts from row 2:

• In cell C2, enter the formula to calculate the total number of days for each assigned group:

=TODAY() - B2

• Drag this formula down to fill the cells in column C for all rows of data.
• In cells D2 onwards, you can use conditional formulas to categorize the total days into different time frames. For example:
• For "<1 Days":

=IF(C2 < 1, 1, "")

• For "<2 Days":

=IF(AND(C2 >= 1, C2 < 2), 1, "")

• For ">5 Days":

=IF(C2 > 5, 1, "")

• Repeat similar formulas for other time frames ("<7 Days", ">7 Days", etc.) as needed.
• To calculate the total count for each assigned group in each time frame, you can use the COUNTIFS function. For example, for "<1 Days", the formula would be:

=COUNTIFS(A:A, "Mohit", D:D, 1)

• Repeat the COUNTIFS formula for each time frame and for each assigned group.
• Finally, calculate the Grand Total for each time frame by summing up the counts for all assigned groups.
• Adjust the cell references and formulas as needed based on the actual location of your data in the worksheet.

Once you set up these formulas, the table will dynamically update whenever the creation date or today's date changes, providing you with the count of days for each assigned group in different time frames.The text was created with the help of AI.

If this is not what you are looking for, please provide additional information as Mr.  Hans Vogelaar recommended.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.