Update the below table using data from data tab1. The data should change dynamically when the date range is changed

Question

Update the below table using data from data tab1. The data should change dynamically when the date range is changed

Date Rage (Submitted Date)
Start date	6/1/2020 1.52
End date
Operator Name	No.of Jobs	Volume	Deadline missed jobs%	Shared jobs	No.of Americas job
Operator 1
Operator 5
Operator 26
Operator 32
Operator 53
Operator 58
Operator 65
Operator 106

Answer 1

The 1st step is to correct the data:

After that we can use simple COUNTIFS or SUMIFS formulas:

C27: =COUNTIFS('Data 1.'!B:B,B27,'Data 1.'!E:E,">="&$C$23,'Data 1.'!F:F,"<="&$C$24)

Sample file: https://1drv.ms/x/s!AsEpmlJLteasji2tf-MdqzVM69aT?e=d5zVlL

Andreas.

Answer 2

Mam, then what is the solution...in What method we can use to solve the solution.....

OR

If we can take it as June'22 month. then how to solve this

Answer 3

Hi Arun,

It is not possible to perform this operations with the data as it is. The reason is that your date/times are text, not real date and times.

"6/1/2020 1.52" is a text, we can guess that this means 6th January 2020 1:52 AM but is can also mean 1st June 2020 1:52 PM or...

Date and times in Excel are in fact a number, for example enter the formula =NOW() into a cell and you'll see the current date and time (based on your lcoale settings). If you format the cell as General you can see the number that Excel uses.

Andreas.

Answer 4

Hello,

As per your guidance, I'm posting the link here....

If possible, Kindly provide the solution for the entire data, or just give me an hint to solve it as per my own knowledge...

https://1drv.ms/x/s!AtDJtHQCbNQ-gXCSib36QXn6aeeF?e=uDguKG

Thanks & Regards

Arun G

Answer 5

It is far too complicated to recreate such a scenario. And if it does not match yours, then our solution will not work for you.

For this kind of requests, please create a sample file with the layout of your original file, filled with sample data and colored cells with the expected result.

At best make a copy of your original file and anonymize the necessary data. For this please download this file
https://www.dropbox.com/s/rkfxuh85j5wyj9y/modAnonymize.bas?dl=1
Open your Excel file
Right-click on the sheet tab
Choose "View Code"
Press CTRL-M
Select the downloaded file and import
Close the VBA editor
Select the cells with the confidential data
Press Alt-F8
Choose the macro Anonymize
Click Run

Upload it on OneDrive (or an other Online File Hoster of your choice) and post the download link here.
https://support.office.com/en-us/article/Share-OneDrive-files-and-folders-9fcc2f7d-de0c-4cec-93b0-a82024800c07

Then we can look at the file and try to find a solution. Thank you for your understanding.

Andreas.