i would like to display all the employee whose passport and visa is almost expired (30 days before expiry) to another sheet. I have done conditional formatting highlighting both fields 30 days before expiry but i want to display the list on another sheet.
View 2 RepliesI have my Employees List in one Sheet and in another Sheet2 I want to Display those Employees Names and List whose Passport going to Expire in the dates entered in Sheet2 A1 and B2 Cell.
View 9 Replies View RelatedBasically I have a list of employees. This list is simple in that it shows their off time "From:", "Till:". in columns to the left of there name. So in Cell A:A would list all of there names.. In the example sheet I have just a few listed. To the left of their name if "From:" (column B) and then "Till:" (Column C). And this From, Till repeats itself for several columns so that multiple off-times may be made for each employee.
Basically what I want to do is delete any sets of dates that are expired (based on the "Till:" date) and shift the remaining dates over. So in other words, if Sam has a date in there of 8/1/09 to 8/1/09 meaning he is going to be off for just that day, and today's date is 8/2/09, then that "from" and "till" date of 8/1/09 to 8/1/09 would get deleted from his list and the remaining dates (if any) would get shifted to the left.
Hopefully I have provided a good enough explanation of what I am trying to do here.. Please let me know if you have any questions! I have provided an example the reflects my setup (and the setup can be changed if it makes anything easier)
I am using Excel 2007.
I am using the coditional formating to highlight whether a date contained in particular cell is within 12 months of todays date or over 12 months in the past.
This is done by placing the =TODAY() in a cell at the top of my sheet and then in another cell, doing a DATEDIF calculation between the two and this works fine. I can then specify in my conditional formating to turn the cell containing the date a funny colour if the cell doing the DATEDIF calc is > or < 12. Simple!
What I can't do is copy this formatting for the other 30 rows of dates I have. When I try and copy them, it references the original cell, not the one for that row. Example: If my date I want to test is in B2 and my DATEDIF clac is in U2, when I copy the coditional format to the new row, say B3, the new rule still references U2, not U3. I understand about dymamic and static references and I have removed the '$' signs from the formula.
In column F I have values (eighter 4 or -4)
Is it possible to use countif to count how many time there is s a switch from 4 to -4 and visa versa.
I have attached the spreadsheet for reference. All other sheets pull information from the WHITE sheet.
I want the columns for unit price, discount and total to be set to two decimal places but I also don't want it to show anything if the field has a 0 value (so if i only have a few items I don't get lots of o's).
To get the field to not display a 0 if it is empty on the WHITE sheet I have been using "0;-0;;@" which I found on the net. If I use this then it negates the two decimal places.
This also includes the WHITE sheet.
=IF(D14="","",MAX(0,EOMONTH(D14,24)-TODAY())&" DAYS REMAINING")
How can I alter this formula to countdown to zero (0), yet instead of reaching 0, its diplays "expired"?
I have an employees database contains IDs expiry dates and other data .. what I want is the following:
1 - Check expiry IDs dates for each employee's.
2- If there is any ID that is already expired or will expired after 15 days from now, its data should be listed in the required sheet.
3- The information that should be listed is the following:-
A- Employee Name.
B- Employee Badge Number.
C- The expired ID date under its ID type as per the attached workbook.
4- If the ID is not expired as per the mentioned above conditions, its place in the Expired ID List should be left blank.
I actually tried the following code, but it doesn't provide the required aim.
I have a row of different dates in a speadsheet all in the same column e.g A1 to A10 What I am trying to achieve is that when it reaches todays date it will automatically change that cell to read Expired.
View 1 Replies View RelatedI attached my spreadsheet. I made a conditional formatting that when Column M is Expired it will highlight the row as red but if it says Pending then it will highlight the row blue. When I try to copy the formula from the 1st rows and drag it down to the below rows. The Status column will say Expired and highlight the entire rows red.
Is there a way to fix this that if valid until column, valid from column and redeemed date is blank then status will just be blank?
voucher.xlsx
I have a huge amount of data 34,000 rows and would like to show it as days of the week.
As an example Dates show as 02/14/2008 10:00. I would like to create a new column indicating days of the week and show as Mon, Tue, Wed, etc.
I tried a couple of functions with little luck (weekday, day), so either I'm using incorrectly or I don't understand the help function.
I have a sheet with a date and the number of months on it which will change. I need the sheet to list the dates in a column for each month automatically: e.g. Two cells contain date “jan07” and the period “10” months. The rows A1 to A10 should have jan07…jan16 listed automatically. If I change then change the number of month to 11 I would like the rows A1 to A11 to update automatically.
View 6 Replies View RelatedI have a spreadsheet that keeps track of staff training. in colum A is the date they completed the training. this training expires in 5 years time. i would like the cell to highlight red when the date has expired. and when i enter the new date in 5 yrs time the cell to return to no fill color.
View 8 Replies View RelatedI need cell A1 data deleted once the date in B1 has passed.
View 7 Replies View RelatedI have a column of dates in one column and in the next column next to the dates, I want to show the day of the week that each date represents, such as;
Col A Col B
1 March 2008 Sunday or Sun
etc etc..
I'm in the process of setting up an Excel document and I need to be able to have it display dates in a specific format. I need it to express just a month and year such that the month is represented by a letter (A thru L) and the year is expressed as its last two digits such as in the these examples:
May 2012 = E12
Mar 2009 = C09
Nov 2011 = K11 etc.
I want the date to come out in this format regardless of how the user enters it.
I have data that shows titles as 2-Feb, 2- Mar; which is really typed in as 2/5/2012 and 3/15/2012. My chart when graphed keeps showing the X-axis as 1-FEB and 1-MAR. How can I get the graph to show the x-axis titleas as 2-Feb and 2-Mar?
View 3 Replies View RelatedIs there anyway for a chart to show only a certain range, ie the past 12 months, without having to update the range every month?
We need to show our scrap rate over the last 3 years. At the end of every month we add a new line. When we enter the number of pieces scrapped for this month I want the chart to drop the number scrapped in January '07
If I have a date which is 03/09/2006. How can I create a formulae to abbreviate this. Ie to return 09/2006 (I am english so we have the DD and MM the other way round to you guys in the states).
In other words if an event happens on the third of september 2006 I want a column which classifies that event as september 2006 with NO reference to the day.
I have the below find and replace code that is now working. I double checked the syntax and can't find a reason why. The errors I get are Overflow and 400.
'format column with custom date format.
Public Sub formatDate()
Dim charHold As Date
What = m / d / yyyy
repl = yyyymd
Cells.Replace What:=What, Replacement:=repl, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
dates column displays numbers even though the format in the format cell options is date format.
the number updates to date format only when i do F2 & enter for each cell. i've about 5000 cells with same problem. how cani do them all in a go instead of doing themone for a time.
I have the months in a drop down menu January to December in cell B1.
Is there a way for excel to display the dates in that particular month based on the selection in cell B1?
For example, if I were to select "February", I'd like Excel to display:
02/01/2012
02/02/2012
...
02/29/2012
in cells A5 through last day of the month?
I would like to use a formula to take the starting date in cell A1 and then have Excel give me a list of all the dates for 2009 excluding Sundays.
So A1 would be 1/01/09
Then Excel would list
Thursday, Jan 01
Friday, Jan 02
Saturday, Jan 03
Monday, Jan 05
Tuesday, Jan 06
etc....
I've tried messing with the WEEKDAY function, but can't seem to figure it out.
I've got 3 problems I've been trying to solve for the last 7 hours, I think they are all relatively simple to those who know what they are doing but I've exhausted my excel for dummies and every formula I have taken from this siteand tried to adapt. Obviously I'll post each problem on a different post, so here's problem one. The sample is attached. It will consist of about 35 sheets, each documenting the hire of a Motability scooter or wheelchair.
On each sheet column P will identify any review dates. What I would like is: On the Master page in Column C next to the relevant sheet name is for it to display any 'Review' that is flagged up, so that the user can have an overview. There will only ever be one 'Review' per sheet, I can only get the master page to show it by referencing it to one cell, I can't work out how to search the whole column.
automating a excel sheet for me. I am actually doing this process manually copying and pasting, but it seems to be taking too much of my time.
I have attached a sample workbook below.
I have a Main sheet where my data is currently present. Right now the data has been sorted accordingly to Track Names.
What i need is I manually copy all the Data belonging to 1 Track into another sheet thats being named under its name. e.g. Aintree sheet will contain all the details found inside my Main Sheet for Aintree track.
This process is currently manual. Is there a way to automate it? Is it possible that my individual track sheets be able to link automatically to reference the mainsheet to display whatever values thats under its track?
Current my Data in the Mainsheet is sorted by Track Name in ascending format. Is it possible for the formula to pick up the correct track automatically without my sheet being sorted, since I have to add data in every data, so my excel keeps on growing.
Have have a excel spreadsheets with 2 columns. One with the employees name and one with the date that their warning expires. I would like the employees name to be automatically removed after the date expires. How do I do this.
View 1 Replies View RelatedI need to show on one page all of the people that are above a certain percentage for the day from largest to smallest, as long as they are over 4.5%.
Unfortunately the sample size will change daily.
on the second page i have the people's names, plus all their data, plus the % metric i'm using for the front page.
so the data page would look like this:
How can I display the sheet name in the sheet itself? My sheets are named as territory numbers and I want cell A1 of each sheet to be something like
="Territory "&....
I know that this can be accomplished in the header/footer section but I'd prefer to have it in a cell if possible.
I have a table with headers C1:I124, Columns E-H are hidden.
Col C = Agent (list of names)
Col D = #Hits (number of times the Agent's name appears on a list)
Col I = #Strikes (Number of times an Agent fails an Audit)
What I want to do is produce a Watchlist split into 4 sections:
1. All the Agents with 1 Strike
2. All the Agents with 2 Strikes
3. All the Agents with 3 Strikes
4. All the Agents with 4 or more strikes
So I want to display this in a table in the same sheet which has 4 columns as described about listing all the Agents who match that criteria.
eg
Strike 1 Strike 2 Strike 3 Strike 4 or more
Bob Ian Larry Reggie
Tim Colin Stephen
Mick Robin
Andy Angela
Laura
I have a document that has a sheet for each project, 3 at the moment. On this sheet are various text & currency fields, 24 in total.
I want to create an overview document for all of the projects so it will display lines from the individual project sheets that are currently showing as NOT complete.
To clarify I currently have 4 categories for status at the moment: Planning, In Progress, Awaiting Inspection Report, Complete. So I want my overview to show anything that is not complete.
I also have no need for all of the 24 columns in the overview either, I've highlighted about 9 at the moment.
I think maybe a lookup and If combination is my answer but I'm not altogether sure if there are other functions out there that I just haven't come across yet, or how to combine an if and lookup.
Also I would like the data to be sorted firstly by Inspection Location and then in date order if possible. I know I could use the auto filter option once the data is pulled across but it was be easier for the other users of this document whose skills range greatly.