Test.PNG

Hi there,

I am trying to create a weekly schedule so I can see what projects my employees are booked on.

The criteria for the formula is to look for the employee name, if the cell under the week is not blank, return the project code.

Thanks for your help

]]>Hi there,

I am trying to create a weekly schedule so I can see what projects my employees are booked on.

The criteria for the formula is to look for the employee name, if the cell under the week is not blank, return the project code.

Thanks for your help

Hi,

I have two data series. I want to know how can I make x=y in scatter plots.

]]>I have two data series. I want to know how can I make x=y in scatter plots.

I need to combine the 3 IF statements below....HELP!!

=IF D5:D200 = A5, F*H

=IF D5:D200 = A6, E/H

=IF D5:D200 = A7, I=G

]]>=IF D5:D200 = A5, F*H

=IF D5:D200 = A6, E/H

=IF D5:D200 = A7, I=G

Hi

I need help below query filter to dynamic dare and time filter in power query excel so that I can filter records of DATE column between a time range given dynamically taking today’s date from 5 Am and tomorrow date 4 AM.

I am doing this manually though following query which is a manual process to filter every day changing date . How can I make it dynamic to take dates dynamically:

#"Filtered Rows" == Table.SelectRows(#"Changed Type", each [DATE] > #datetime(2018, 6, 29, 5, 0, 0) and [DATE] < #datetime(2018, 6, 30, 4, 0, 0))

]]>I need help below query filter to dynamic dare and time filter in power query excel so that I can filter records of DATE column between a time range given dynamically taking today’s date from 5 Am and tomorrow date 4 AM.

I am doing this manually though following query which is a manual process to filter every day changing date . How can I make it dynamic to take dates dynamically:

#"Filtered Rows" == Table.SelectRows(#"Changed Type", each [DATE] > #datetime(2018, 6, 29, 5, 0, 0) and [DATE] < #datetime(2018, 6, 30, 4, 0, 0))

Hello everyone. I am new (as of today) and have a MS Excel question(s). This is two fold.

(1) I need to count the number of applications per agent that reach the target count of 25.

(2) Of the same data, I need to count how many applications an agent had within 30, 60, 90 and 120+ days.

I thought about using =COUNTIFS, but I have a target count involved. I have a possible idea for a formula to put into an Excel cell, but I seem to be over thinking.

My data is as follows:

Column A - Agent Producer Number

Column B - Agent Name

Columns C - BE Production dates (headers) then the Policy count per day, per agent.

Hopefully I explained my question clearly. :) Thank you in advance for any assistance. FYI. . prefer a formula vs VBA. See the attached file for the data being used.

(1) I need to count the number of applications per agent that reach the target count of 25.

(2) Of the same data, I need to count how many applications an agent had within 30, 60, 90 and 120+ days.

I thought about using =COUNTIFS, but I have a target count involved. I have a possible idea for a formula to put into an Excel cell, but I seem to be over thinking.

My data is as follows:

Column A - Agent Producer Number

Column B - Agent Name

Columns C - BE Production dates (headers) then the Policy count per day, per agent.

Hopefully I explained my question clearly. :) Thank you in advance for any assistance. FYI. . prefer a formula vs VBA. See the attached file for the data being used.

Hi,

We use a Workbook for recording Vehicle times outbound (Loading Time, Day, Driver, Destination, Vehicle Registration Etc. Etc.)

The workbook has 1 tab for each day of the week, 1 Spreadsheet for each Week, 4 Spreadsheets for each Period.

I would like to build a simple 1 tab spreadsheet as a lookup feed for Sunday Commencing dates, All Vehicles Registrations, Trailer ID's, Employees.

Basically a list for each that feeds the dropdowns on each spreadsheet above (Data Validation)

How would I go about linking the feed in Office 365 Excel/SharePoint? (excel data validation list from a central source feed spreadsheet stored in the same OneDrive folder)

Is it possible?

Is there any workarounds for such a task, would SharePoint Lists help?

Thanks in advance

Trunking Log.jpg

]]>We use a Workbook for recording Vehicle times outbound (Loading Time, Day, Driver, Destination, Vehicle Registration Etc. Etc.)

The workbook has 1 tab for each day of the week, 1 Spreadsheet for each Week, 4 Spreadsheets for each Period.

I would like to build a simple 1 tab spreadsheet as a lookup feed for Sunday Commencing dates, All Vehicles Registrations, Trailer ID's, Employees.

Basically a list for each that feeds the dropdowns on each spreadsheet above (Data Validation)

How would I go about linking the feed in Office 365 Excel/SharePoint? (excel data validation list from a central source feed spreadsheet stored in the same OneDrive folder)

Is it possible?

Is there any workarounds for such a task, would SharePoint Lists help?

Thanks in advance

Trunking Log.jpg

Hi,

I am trying to transpose a large amount of data. My problem is below.

I am trying to transpose columns G-J...so I do the following {=transpose(G10:J10)} which works exactly how I want it to.

What I need to do know is click and drag the formula down so that the next data I want to transpose it skips to {=transpose(G14:J14)} and so on when I really want it to go to {=transpose(G11:J11)} and so on.

Please Help!!

]]>I am trying to transpose a large amount of data. My problem is below.

I am trying to transpose columns G-J...so I do the following {=transpose(G10:J10)} which works exactly how I want it to.

What I need to do know is click and drag the formula down so that the next data I want to transpose it skips to {=transpose(G14:J14)} and so on when I really want it to go to {=transpose(G11:J11)} and so on.

Please Help!!

Hello Forum,

I am intermediate Excel User and need help with below

My employees log their task hours through out the day and week (even on weekends and holidays)

My "Y" column contains start time of their task and "Z" column contains end time of their task.

Now my office working hours are Weekdays 0900-1800, excluding holidays.

Now using the start time and end time they logged, I need to find, how many hours they worked outside 0900-1800 (that is working hours)

If it is a weekend or holiday then that whole task time is outside working hours.

Scenario 1: If a employee starts it's task at 1600 on a weekday and ends the task at 2100. He worked total of 5 hours but only 3 hours are outside working hours. I need these 3 hour calculation

Scenario 2: If a employee starts it's task at 1600 on weekend/holiday and ends the task at 2100. He worked total of 5 hours and all these should be considered outside working hours.

Please help with your expertise

Thanks in advance!

I am intermediate Excel User and need help with below

My employees log their task hours through out the day and week (even on weekends and holidays)

My "Y" column contains start time of their task and "Z" column contains end time of their task.

Now my office working hours are Weekdays 0900-1800, excluding holidays.

Now using the start time and end time they logged, I need to find, how many hours they worked outside 0900-1800 (that is working hours)

If it is a weekend or holiday then that whole task time is outside working hours.

Scenario 1: If a employee starts it's task at 1600 on a weekday and ends the task at 2100. He worked total of 5 hours but only 3 hours are outside working hours. I need these 3 hour calculation

Scenario 2: If a employee starts it's task at 1600 on weekend/holiday and ends the task at 2100. He worked total of 5 hours and all these should be considered outside working hours.

Please help with your expertise

Thanks in advance!

Excel file got highly corrupted. I had done all the basic recovery things. Even tried some repair programs and online repairing tools. This is an important excel sheet of the financial*department. Kindly please help someone to repair my.xlsx file.

Office version: Office 365 latest

Saved Location: One drive

Office version: Office 365 latest

Saved Location: One drive

is there a way to speed up the updating and realtime collaboration on excel 365 ? unfortunately it takes more than 20 seconds to update between users currently on my excel sheet.

]]>Hello,

I have a TXT files which has timestamps with milliseconds. The problem is that all values which are less than three digits show up without leading zeroes. So, for example:

2019/9/24, 15:02:08.8

2019/9/24, 15:02:08.9

2019/9/24, 15:02:08.10

2019/9/24, 15:02:08.11

When I try to load the data in excel using h:mm:ss.000 it just appends zeroes to the digits, which is wrong. The problem is compounded by the fact that I also have millisecondd values later on with three digits (i.e. 2019/9/24, 15:02:08.100, 2019/9/24, 15:02:08.101, etc).

Is there a way for me to load the data and fix the column so that all millisecond values show up correctly?

Thanks in advance!

]]>I have a TXT files which has timestamps with milliseconds. The problem is that all values which are less than three digits show up without leading zeroes. So, for example:

2019/9/24, 15:02:08.8

2019/9/24, 15:02:08.9

2019/9/24, 15:02:08.10

2019/9/24, 15:02:08.11

When I try to load the data in excel using h:mm:ss.000 it just appends zeroes to the digits, which is wrong. The problem is compounded by the fact that I also have millisecondd values later on with three digits (i.e. 2019/9/24, 15:02:08.100, 2019/9/24, 15:02:08.101, etc).

Is there a way for me to load the data and fix the column so that all millisecond values show up correctly?

Thanks in advance!

Hi!

I'm trying to use a SUM that is working on its own in a large formula that I need to operate as an array (CSE) but inside that long formula it just calculates 0.

This is the sum I want to use: SUM(VALUE($Q$2:INDIRECT("Q"&ROW()-1))) - Basically I want to sum up all the values on top of a given one in a column.

When I include it in the bigger formula and I evaluate it, I get SUM(#VALUE) so result is 0, but when I use it in a normal cell, even if I execute it as CSE, it works.

As for the part in the bigger formula that needs to be operated in an array is this one:

SUM(IF(CONDITION,1/RANGE)) - I'm trying to sum the reciprocals of certain values if they meet a condition (1/n+1/m+1/p...etc) This is the only way I've managed to do it

Any ideas on why this is happening?

Thanks!

]]>I'm trying to use a SUM that is working on its own in a large formula that I need to operate as an array (CSE) but inside that long formula it just calculates 0.

This is the sum I want to use: SUM(VALUE($Q$2:INDIRECT("Q"&ROW()-1))) - Basically I want to sum up all the values on top of a given one in a column.

When I include it in the bigger formula and I evaluate it, I get SUM(#VALUE) so result is 0, but when I use it in a normal cell, even if I execute it as CSE, it works.

As for the part in the bigger formula that needs to be operated in an array is this one:

SUM(IF(CONDITION,1/RANGE)) - I'm trying to sum the reciprocals of certain values if they meet a condition (1/n+1/m+1/p...etc) This is the only way I've managed to do it

Any ideas on why this is happening?

Thanks!

Dear Friends,

PFA

Is there any formula which can produce the output mentioned in col J, K, L?

There are approx. 35 columns and 100 rows. The issue is sequence of columns keep changing. But output will remain the same.

Please assist.

PFA

Is there any formula which can produce the output mentioned in col J, K, L?

There are approx. 35 columns and 100 rows. The issue is sequence of columns keep changing. But output will remain the same.

Please assist.

Is there a way to remove "." in a string of numbers? is it a formula? For example, number looks like this 9999.00.0000 and I need to remove all the .'s How is the best way of doing this?

]]>