I need a formula that will search between dates and time to identify if the person will be present at the conference breakfasts, lunches and dinners.
Therefore the cell needs to look if the current date falls within the arrival and departure date and also if the time within eating times falls outside the arrival and departure times.
I have 3 spreadsheets I combined into one,
They are voters for 3 separate occasions. I would like to filter out how many people have voted 2 times or 3. I have sorted the information with 2 columns "LASTNAME" and "FIRSTNAME"
Example:
LASTNAME FIRSTNAME
ABBOTTJANET
ABBOTTSTUART
ABBOTTJANET
ABBOTTSTUART
ABBOTTJANET
ABBOTTSTUART
These first 3 show up 3 times, so I would like to take all the info from row one and two to get all the info for Janet and Stu,
I have a formula that I use to tally data and return the number of times a particular value is present in column C.
IF(COUNTIF($C$2:C2,C2)=1,SUMIF(C:C,C2,E:E),"")
I copy this formula down the sheet until arriving at the last input in column "C".
Can someone tell me how to write this in code. All I would like visible in Column "F" is the output not the formula.
I have a spreadsheet with ~350 people tracking 24 dates for classes each. On a seperate tab I have a macro that copies and pastes a pre-defined macro to display the due dates. My question is, can I have a macro run the calculation and put the result in the cell rather than have around 8,400 calculations on a sheet?
View 9 Replies View RelatedI am using this formula to count the number of times “closed” appears between
particular dates:
=SUMPRODUCT(--($B$1:$B$23=F1)*($C$1:$C$23="Closed")*($A$1:$A$23>=$I$2)*($A$1:$A$23<=$J$2))
I have tried applying the same logic to another formula where I wanted to
Also count the number of times “Not Stated” and “In Progress” are shown.
However when I do I am receiving a ‘0’ number in return. The formula I wrote
was:
=SUMPRODUCT(--(CS_Ticket_Report_Dump!D$1:D$50000=C6)*
(CS_Ticket_Report_Dump!G$1:G$50000="Closed")*
(CS_Ticket_Report_Dump!G$1:G$50000="In Progress")*
(CS_Ticket_Report_Dump!G$1:G$50000="Not Started")*
(CS_Ticket_Report_Dump!A$1:A$50000>=AN$1)*
(CS_Ticket_Report_Dump!A$1:A$50000<=AO$1))
Count people per their classification between two dates. Spreadsheet attached.
The answer I need to show in column (S) is:
Cell (S4) = 1 (supervisor)
Cell (S24) = 1 (fitter)
Cell (S36) = 3 (rigger)
j906 count.xlsx
A
B
1
Terry lives in England
=FORMULA
2
Claire lives in Spain
3
Paul live in France
I'm looking for a simple formula within the cells of column B to populate the cell with data based on the "country" mentioned in the string of cells in column A. There will be 20 countries and dependant on the country mentioned a 3 character country code will be entered in column B allowing for easy filtering of people based on country code. so something along the lines of if A1 contains "England" B1 equals ENG etc (for abot 20 countries)
The output should look like below
A
B
1
Terry lives in England
ENG
2
Claire lives in Spain
ESP
3
Paul live in France
FRA
In a project i am compiling i need to work accurately with times to calculate the work progress of the people in the workshop thus....here goes....
I have in work book #1 (7) sheets mon to fri + complete week + a sheet where all job numbers are collected.
From monday to friday the workmen log their times as a start time and a end time. This has to be then calculated to a total hours:mins spent per job, wich in turn then has to be calculated to a total hours:mins spent per day. And the on the complete week sheet recalculated as a total time worked per week.
What I need to do it create a traffic light icon, just the ones in Excel based on the dates within the field. If there is a date present which is past todays date then I would like it to display the green traffic light icon, if the date present is scheduled for a future date I would like the amber traffic light to show, and finally if there is no date present at all I would like it to display a red traffic light icon.
View 1 Replies View RelatedThe following code fills down column B for rows 3 to 110, regardless of the inserted "If Not IsEmply' statement. I've got formulas in Column A from row 3 to 110, but visible values in rows 3-5. I want it to fill the for the visible values only.
View 2 Replies View RelatedI'm looking for a formula that will calculate the difference in times between specific times while working with a 24 hr clock. Please see details below:
E3 provides the start time of 4:00
H3 provides an end time of 15:30
If an employee works betwen the hours of 0:00 (midnight) to 5:59, this is considered DIFF hours and is therefore the number I am seaking. So for the data noted above, the total DIFF hours worked is 2 hours.
I have a macro that searches and replaces dates (both text and date formats) in all files in a folder. It works if I initiate it from the VBA Editor, but not if I initiate it from the file.
It seems that the worksheet is not activating. Please take a look and let me know what I can try.
Things I have already tried: Window methods, such as minimizing the workbook that contains the macro, activate next, etc. Also putting the macro in Personal.xls and retrieving the values from the other file.
Where it hangs is right after the first workbook in the folder is opened. I tried to get it to select A1 in the active sheet but that cell is not selected in either the macro workbook or the newly opened workbook.
Also, if there is an easy way to exclude my macro file (name contains ZZZZ) from the FileSearch............
I've created a spreadsheet that I use to calculate weights of steel members for my job for estimate purposes. I'm trying to figure out how to save some time by not having to enter/copy/paste formulas every time I do takeoff for an estimate.
The bold green cells in column K are always linked to the yellow "Part Heading" above in Column A. As I do my estimates, I copy and paste a "Part Heading" with a "PL/Member" formula below for the next piece I'm working on. Every time I paste a new "Part Heading" with a "PL/Member" formula, I must change the formula in Column K to link to the next cell up in column A with a value present. In the screen shot, cell K/90-K/96 are linked to cell A/89. Cell K/83-K/87 are dependent cell A/82; K/74-K/80 are dependent on cell A/73, and so forth. Since each "Part" varies on total number of "Members", I can't seem to write a formula that will stick when copying and pasting.
My question is: Is there a way that I can automatically link each cell in Column K to the next cell up in Column A with a value present?
Which formula should I use to look for the present value factor if I have the interest rate and the # of periods. attached file.
I work on csv files which are created everytime someone makes a change in certain parameters used by a robot, however when opening these in excel they are nonsensical.
This can be changed with a few macros etc. but my manager would like it to be a "press one button and it works" procedure.
So what im looking for is some sort of function that checks if there is any data in the next column of the spreadsheet and runs a macro in that case and only that case.
I have a file with two sheets.
Sheet1 Data are as follows :
Column A : Date (Data is : 31/01/2006,01/02/2006 ...... )
Column B : Name (Data is : ABC , XYZ, ABC .......)
Column C : Time (Format is : HH:MM) (02:00 , 03:00 .....)
Sheet2 Data are as follows :
A1 : Start Date and B1 : End Date
A2 : Name and B2 : Sum of Time
A3 : ABC and B3 : (i Want Result in this cell)
Result is :
I want Result in B3 cell : Name(ABC) of Time sum from sheet1 Column C between Start date(A1) to End Date (B1).
I need a formula to give me a MIN or MAX value only if there is a number greater than 0 in the cell AND not alphabetical letters (as some cells do contain). I need it to return the value for that cell across about ten sheets--I have that part figured out (I think). But I keep getting a #Ref error.
View 9 Replies View RelatedI have a spreadsheet that has times in G column in military time. Some of the entries have "##:##" while others have "###" or "####" with no colons inserted.
I want to search through the g column and convert "###" to "#:##" and "####" to "##:##"
If you have some low time resolution data you need to compare with high time resolution data how do you average the high resolution data. For example data that starts and ends a fortnight apart and you want to compare it with data that starts and ends 1 hour apart you need to average the hourly data to do so. I can't figure out how:
So if I had Sheet 1 with hourly data
A B C D E
1 Date, Time, Measurement
2 01/01/2014, 01:00, 62
3 01/01/2014, 02:00, 38
etc....
Then in Sheet 2, fortnightly data
A B C D E
1 Date on, Time On, Date Off, Time Off, Measurement
2 02/01/14, 15:23, 18/01/2014, 12:25, 52
3 18/01/2014, 12:25, 01/02/2014, 14:19, 34
etc....
I'd want to add a new column of data to average the hourly data so I can use the correl function.
In one column I need dates and every date has to appear 16 times.
So, I need date 2009-06-03 in 16 rows, in next 16 i need next day - 2009-06-04.
I have A1= Date A, B1 = Time A, C1 = Date B, D1 = Time B, E1 = Hours between Date&Time B and Date&Time A
How can I enter a formula that takes the date and times from separate columns and calculates them?
I have a table that I am storing into an array. I have an array FDate where I store a date and another array FTime where I store times.
FDate is formatted as m/dd/yyyy Hh:Mm: Ss
Ftime is formatted as Hh:Mm:Ss
I know in excel that you can add and subtract dates and times. How do you accomplish the same task in VBA?
I'm trying to get excel to calulate the difference using the following format
dd/mm/yyyy hh:mm
dd/mm/yyyy hh:mm
So really I need excell to tell me what the actual difference is between dates
I seem to have the worst luck when it comes to getting dates and times to calculate together. My logic just seems to go straight out the window.
What I am trying to do, the end result, is get a Time Worked (duration) that I have spent on a ticket, and a Total Time, the time a ticket was opened, to the time it was closed.
I guess we can try to do this one step at a time.
The first thing that I would like to know is if there is a way to calculate Date/Time in a MM/DD/YYYY, H:MM format?
IE:
Ticket Opened ...................Ticket Closed
5/16/2008 2:54 PM..............5/19/2008 10:47 AM
Total Time in Days / Hours / Minutes
Right now I have the Date and Time split into separate cells, simply because I couldnt figure out a way to subtract the combined date and time.
I am trying to create a "daily donut" to track how long it has been since I left for my current deployment, and how much time I have left until I return home. I would like to show exactly how many year(s), month(s), day(s), hour(s), minute(s), and second(s) (like 1 year, 4 months, 4 hours, 5 minutes, & 34 seconds not 1.0 year, 0.33 months, etc.) it has been (again, not 3600 seconds, 60 minutes, 1 hour, etc.), and how many I have left. I have spent many hours trying to figure this out, and I have even tried looking through this forum, but I still can't completely figure this out. I was close in figuring out the date differences in the x amount of years, months, and days format, but it wouldn't account for the time. I.E., 06/03/09 05:00:00 AM - 06/01/08 06:00:00 AM, would show 2 days, instead of 1 day (I'm not sure how to incorporate the time yet).
The variables are:
A1 Date I Left For My Deployment in M/DD/YY HH:MM:SS AM/PM
A2 =Now() Function in Same Date & Time Format as A1
A3 = Date I Get Home from My Deployment in Same Date & Time Format as A1
Any ideas or help? And if possible, I would like to be able to do this using excel's built in functions (not excel add-ons, vb, etc.), so that it would work on government computers.
I receive data from a company I deal with that has the date and time of all of my transactions with them listed in an excel worksheet. Only problem is that the date/time that I receive is not in my timezone, which is a bit of a problem for me!
What I want to do is to be able to adjust the date and time shown in the cell forward by 14 hours, so that the data is shown in the timezone that I am currently in. So the end result for the first line of data in the attached file would end up being 2006-08-11,10:28:51 instead of 2006-08-10,20:28:51.
Every week, in row 3 i enter the number of boards cut. In row 9 i want a running total, but until this weeks total is entered, i dont want anything to show. This is the data for a bar chart which i print off every week. Next week is week 3 and i will enter the data in cell d3. When i do i want cell d9 to show the new total of B3,C3,D3. And so on each week.
View 6 Replies View RelatedI have a list of times in a column. I'm looking for a macro that will go down the column and record all the times that lay between 5 and 7am, 1 and 3pm (13:00-15:00) and 8 and 10pm (20:00-22:00)
How to write it?
calculating project completion % based on Project start date and end dates .
View 5 Replies View RelatedIs it possible to seperate this sheet by Time - using column F, anything which is between the hours of 07:00 - 09:00 and 16:00 - 19:00? Response - column I - times up to 60 mins - 61 - 120 mins and over 120 mins
View 5 Replies View Related