IF Function Inform If Person Worked Over 6 Months In 2008
Feb 1, 2009
I have attached a worksheet that shows the last and first name of an employee along with their hire date and term date, if termed. What I am looking for is a function that will tell me if a person was active as of 12/31/08 which will read true on the side of their name under active; I also need a function that will tell me if a person worked over 6 months in 2008.
The first person John Doe was hired in 2005 and has a true on the side of his name which meant that he was active as of 12/31/08 but as you can see Jane Doe has true on the side of her hire date and false on the side of her term date and I don't want it to read the first true but the last true of someones name, but Jane Doe qualifies for working over 6 months even though she started in 2007.
I'm trying to create a macro that will take name values from a list (Last name, First name) and then find these particular names in a long list of names in another sheet. Once all the occurrences of the name are found, the macro would need to add up all the hours that the person has worked during a given month. For example, I have Jane Doe and John Smith on my shortlist, the macro would have to take the name of the first person, Jane Doe, find all the occurrences of that name in the second sheet that contains names, dates and hours worked, then add up all the hours worked by that person and return the total hours worked value for that parson. Then take the name of the second person, John Smith, and do the same, and so on. Is it possible to do it with macros?
I need to be able to add up times worked for 12 months of the year. For example if one workd 49 hours and 23 minutes the month of Jan and the same for Feb, how do I do this and keep a running sum of them? I can't see to figure out how to format it so that it does not change to time of day since it will be over 24 hours.
I am working on a sheet that will allow a user to enter a start month (from a pre-defigned drop down list) and an end month (again from ddl). What I need to calculate is in what months the employee worked in so I can calculate their salary cost by quarter.
It can populate other cells in order to do the calculation if needed. So, for example if an employee started in May, and worked until December, I need to calculate that he worked 2 months in Q1, 3 months in Q2, 3 months in Q3, and 0 months in Q4. Our fiscal year is April to March.. ;-) I have thought of several options, but none of them have worked 100%.
I'm struggling with the VBA DATEDIFF function. I have a person's DOB and am attempting to determine their age by comparing to a different date. It seems like their age only changes when I change the year, rather than choose a date within the year past their birth date... Here is my code:
Sub Test_click() DOB1 = #7/19/1930# 'DOB date2 = #7/18/2012# Date3 = #7/21/2012# age1 = DateDiff("yyyy", DOB1, date2) 'age should be 81 (but is 82) age2 = DateDiff("yyyy", DOB1, date2) 'age is 82 (correct) End Sub
I'm trying to create a work Rota and I'm having a bit of a problem with a certain section. In Worksheet 1 I have the following headings:
Cell A - Name Cell B - Monday Cell C - Tuesday Cell D - Wednesday etc
Under these headings is each member of staff and the hours they work, IE L (Late), E (Early), SD (Short Day) etc. In the final Column, it counts the number of hours that this person works (Early is 7.5). In Rows 46, there is a section here to work out how many people are working earlys, which is where I have the problem.
The hours are worked out by doing a lookup function on the cell that says E, L etc and goes to a CODES sheet and pulls the value of that letter. What I am wanting to do is lookup that Letter, which and look in the cell next to it and count how many people would be on an early etc.
Need a way of quickly telling whther a user as a workbook open without trying to open the workbook yourself? I have many individual workbooks which I need to regularly update, before I can update them I have to check whether anybody else has them open and then wait until they are out before I can do the update.
I would like to know if I can set a formula or macro to say that if a value in a dropdown list equals, lets say "Business", then show this list of value in a dropdown list in this cell.
I'm using a UserForm to inform the user that the program is still running. Unfortunately, when I do: UserForm1.Show the program hangs there until I hit the Ok button. How can I make the program run as the box is shown.
Is there a way to conver a persons time spent (given in weeks) to adjust/convert to show per month. Attached is the sheet. Do note that week 2/25 - 3/1 is a combination of Jan and Feb so hours should be logically divided into jan and feb...
Name 2/18 - 2/22 2/25 - 3/1 3/4 - 3/8 3/11 - 3/15 Feb mar Tom 40 10 0 20 ?? ?? name 2/18-2/22 2/25 - 3/1 3/4 - 3/8 3/11-3/15 Feb Mar
I have 20 sheets in a work book (1-20) with similar row details(80 rows)...but the columns have amounts for 12 months. I would like to do a sum if function based on months chosen.
For eg: If I select "sheet 1" and choose month as june...I want the sum of amounts from Jan-June.
I had more than 7 nested if statements so I attempted to formulate the named ranged formula. My 2 ranges are as follows: LDD1=IF(MONTH(P1)=MONTH(F2),C5-F5,IF(MONTH(P1)=MONTH(G2),C5-G5,IF(MONTH(P1)=MONTH(H2),C5-H5,IF(MONTH(P1)=MONTH(I2),C5-I5,IF(MONTH(P1)=MONTH(J2),C5-J5,IF(MONTH(P1)-MONTH(K2),C5-K5,IF(MONTH(P1)=MONTH(L2),C5-L5))))))) LDD2=IF(MONTH(P1)=MONTH(L2),C5-L5,IF(MONTH(P1)=MONTH(M2),C5-M5,IF(MONTH(P1)=MONTH(N2),C5-N5,IF(MONTH(P1)=MONTH(O2),C5-O5,IF(MONTH(P1)=MONTH(P2),C5-P5,IF(MONTH(P1)-MONTH(Q2),C5-Q5))))))
Basically what it's doing is comparing the current month with the month in the column heading and calculating appropriately. However, when i input the if statement (=if(LDD1,LDD1,LDD2), i get an error saying that it's an inconsistent formula and the result that it gives is wrong, too. Problem cell is e5
I have a 1000x1000 cell grid on one of the worksheets in my workbook where each cell contains one of the following four possibilities:
1) Blank 2) A string that is not defined in a lookup table (*lookup table = a named range on a different sheet) 3) A string that exactly matches an item in the lookup table 4) A long string that contains an item in the lookup table (which can be found with a FIND function, for example)
I would like to apply conditional formatting to my 1000x1000 grid, to color each cell according to the lookup table, where the last column has the name of a color that I can match with my conditional formatting setup.
I have solved everything except for No. 4).
2) is taken care of by using "Format only cells that contain" with "No Blanks"
My solution for 3) - using "Use a Formula to Determine Which Cells to Format" - uses =VLOOKUP(A1,lookup_table,2,FALSE)="fill color name" So, for every different color I want to use, I have a different Formatting Rule in the Conditional Formatting dialog.
But 4) is tricky, because VLOOKUP can only return the result for a specific cell value. If my cell contains France;Germany;Italy, I'd like the color to be according to what I set for "France" in my lookup table. Or, if my cell contains Germany;France;Italy, I'd like it to be set based on what I decided Germany is in the lookup table (i.e. "blue").
You can notice that one thing that is consistent is that I separate my "sub-strings" that are contained in the lookup table with a semi-colon.
So, one way might be to see if FIND finds a semi-colon, and then grab the string that goes before that character position.
I have a file with 3 sheets with Sales data. First sheet is for capturing Sales data, second sheet, with some Weekly Analysis and Monthly Analysis using COUNTIFS, third is charts based on second sheet's formulas.
When I use COUNTIFS function to capture the monthly sales nos, the working days in different months vary from each, obviously. Some months have 20 days, some with 21 days, some with 22/23 days. How to write a correct code for varying working days, because I have to capture only the working days in a month, and in THAT MONTH only, leaving all Sat days and Sun days?
My formula goes like this in cell E3 and I have attached my sample file as well.
eg: If I want to measure Monthly Sales performance for a salesman, Date of Sales Performance measure will be done on the last WorkingDay/WeekDay of that month, in the month Sep-2013, Performance will be taken on 30-Sep-2013, till the 21 weekdays backwards. i.e. 02-Sep-2013. In the month of Aug or Feb the no of weekdays differs.
I have added 3 tables data .. also I had created a sample solution calc for emp1 and project1 ... I need to calculation the ratio between the months based on the working days and allocate the efforts accordingly.
I have Mac 2008 Excel. For my work, I download .asp files and open them in Excel. No problem. The problem is... I'm wanting to use another computer at work that has Mac 2011. When I open the exact same .asp files, they show coding (html?). I can fix this by changing the name of the file extension from ".asp" to ".xls." But I convert these files a lot during the day and this is an extra step I don't want to take. And it works fine in version 2008. So what can I do in version 2011 to make it open the files without all the coding?
I would like to convert them to AppleScript so I can use them in Excel 2008.
From what I understand I should be able to do this with AppleScript, but I have never used it before and have absolutely no idea what I am doing.
Not only do I need to know how to convert the VBA to AppleScript, I need to know what to do to make it run in Script Editor, etc.
There are two VBA Macros, each is to highlight a specified cell range, then turn on cell protection.
It is my understanding that although I couldn't record this into my VBA Macro, AppleScript will also let me unprotect the worksheet at the beginning of the script, and then re-protect it at the end of the script.
Placing the date (1-JAN-2008) in the right column for DAY
Now I have a Matrix in which I have the Days as Colum Headings and the Week Numbers as the Rows.
I will be mentioning the Date let's say 1-JAN-2008 in the Cell A1 and I want this date to appear in the approriate Column for the DAY For Ex:-
The day for the date 1-Jan-08 was a Tuesday, therefore the date should appear in the Column having a Heading Tuesday and in the Column Monday it should show as 31-Dec-07 and the Columns for Wednesday,Thursday,Friday,Saturday and Sunday the Following dates after 1-Jan-08 (i.e. 2-Jan-08, 3-Jan-08, 4-Jan-08, 5-Jan-08, 6-Jan-08).
The appearance of the Date should change as per the day-date combination and as such for the preceding as well as following dates..
Ex:- Lets say for 1-Jan-09 which comes on a Thursday it should display (1-Jan-09) under column Thu and Mon,Tue,Wed should display ( 29-Dec-08,30-Dec-08,31-Dec-08) and Friday,Saturday and Sunday the Following dates after 1-Jan-08 (i.e. 2-Jan-09, 3-Jan-09, 4-Jan-09).
I am running XL 2008 on a Mac and have created a pivot table that works well. However, without VBA (and I don't know how to write in Applescript), is there a way to put a REFRESH button on my spreadsheet?
Expand the explanation for the previous thread? [URL]....
I am trying to do basically the same thing, I have a long list of clients and need to separate them by date they contacted us. I am using Excel 2008 on a mac, which does not allow micros. the previous thread does exactly what i need to do, but i cant get it to work.
I am trying to get a grip on my gas n electric bills. My problem is in creating a formula to convert the info I have into the final bill.
The info I have is -: days of use, amount of G n E used, standing charge, price/unit and discounts applicable. For simplicity I have converted them all into a rate per day and £GB. I tried the following formula
:-(standing charge per day * days used)+(cost per KWH * No.of KWH used) - (discounts * days used) / (100) * (94)
This last one is to account for a direct debit 6% discount. I used cell address for things like No of days usage and KWHs used in the period. The values in these cells have themselves been calculated via a formula from other info. The results I get seem to be a constant variable which indicates that I am doing it wrong.
