Displaying Cell Depending On Date
Apr 24, 2007
My head hurts from working on this all day. I have a large spreadsheet and in the top left corner I need to show the percent of vehicles that are not working out of our whole pool. I have a function that detects the color of the cell (Red is broke) and counts the total of red cells and then divides it by the total giving the percent and it is in G118 for Jan 1/2007, H118 for Jan 2/2007 etc. I made a function that will count Julian days from Jan 1, so for today I get 114. I know I need to display G + 114 columns but have no idea how to get that column name from this and always display the current one in the corner.
View 9 Replies
ADVERTISEMENT
Jun 3, 2009
I have 3 'speedometer' type images one for green, one for amber and one for red. I need to display ONE of these on a number of occasions depending on the value of a cell.
E.g.
If the value is 8.0 or above then I want to display the green one
If the value is 4.0 or above but below 8.0 I want to display the amber one
If the value is less than 4.0 I want to display the red one
View 9 Replies
View Related
May 9, 2004
Possible to write code to hide selected sheets in an excel file depending on which user is using the file i.e. hiding sheets that are not applicable to that person while just showing the applicable sheets?
View 2 Replies
View Related
Nov 12, 2009
Each time my spreadsheet is modified and saved, I would like the date of this action to appear in a cell. I think this may involve VBA but I'm not sure.
Let's just say, the cell is L54. I am using Excel 2007.
View 9 Replies
View Related
Dec 31, 2013
I have an excel sheet which looks similar to below and with some more columns.
HTML Code:Â
AreaBranchEmpolyeesProductsShelfs
Area1Area1 Branch110Product 110
Area1Area1 Branch25Product 16
Area1Area1 Branch38Product 28
[Code] ......
I want to have data in other sheet With respect to Product details. Where It gives me the count of Product 1 and so on.
I am able to get the count using countif function and it displays like below.
HTML Code:Â
Product Count
Product 16
Product 28
Product 34
Product 49
Product 510
Product 68
Product 78
Product 89
Product 97
Product 108
What I want is when I select "Product 1" It should display some selected columns data from sheet where complete data is available.
HTML Code:Â
Branch Empolyees Other
Area1 Branch1 10 abc
Area1 Branch2 5 xyz
Area1 Branch5 7 pqr
Area2 Branch5 5 wxy
Which function we can use. I tried Vlookup but was not successfull.
View 14 Replies
View Related
Jul 11, 2009
How can display the last modified date and time of my excel file in a particular cell - Say in cell E1.
This is the same value that we get when we Right Click excel file->Properties->General and look at the label "Modified".
View 8 Replies
View Related
Apr 23, 2012
I have a Cell and the Formula for my Cell is the Following:
Code:
="Week Ending " & 'A01'!D248
The A01 Worksheet displays the value as 4/21/2012 . However, the first worksheet displays the value as
Code:
Week Ending 41020
rather than
Code:
Week Ending 4/21/2012
What can I do to display the cell the way that I want to? I've already tried formatting the cell directly as Date to no avail.
View 3 Replies
View Related
Jan 9, 2013
I am trying to create a sheet in XL 2010.
In Column A each cell will contain a date (differnet from other cells in that column) when inspection was last done.
Column B is when the weekly inspection is due.
Column C is when bi-weekly inspection is due.
Column D is when monthly inspection is due.
Column E is when 6 monthly inspection is due.
I need a formula to change the colour of cells B, C, D & E when each inspection is due depending on the date entered in A
I am hoping its possible that the cell colour can stay for 2 days after the due date and then return back to blank after the second day.
For example if cell A1 has a date of January 1 2013 then on January 8 2013 cell B1 turns red then on January 10 2013 the cell returns back to normal.
A1
B1
C1
D1
E1
Inspection Date
Weeekly Due
Bi-WeeklyDue
Monthly Due
6 Monthly Due
January 1 2013
Change red Jan 8 & return blank Jan 10
Change red Jan 15 & return blank Jan 17
Change red Feb 1 & return blank Feb 3
Change red June 1 & return blank June 3
View 3 Replies
View Related
Jun 11, 2009
The spreadsheet attached is a timesheet I use. The last column on the sheet (IV) keeps a running total of time spent on each project. I need a way that i can find out how much time was spent on projects in specific timeframes (eg 01/01/09 - 07/01/01, or 01/01/09 - 31/01/09). my initial thought is perhaps i can have a "from" cell and a "to" cell to input the dates i require and it will total the relevant cells in the rows below.
NOTE! This workbook uses VBA to auto hide any column which are dates previous to today's date so you may need to unhide them to view times logged.
View 2 Replies
View Related
Jan 15, 2014
I inherited a worksheet with 70k+ rows of data. As shown below each row contains a record number(Col A) with the date(Col B) it was created and value(Col C). There are 5 records per day. You can see in the example below that Col A has the value 30 duplicated for each day. Is it possible to rename only the second "30" in Col A for each day?
Col A
Col B
Col C
10
1/14/2014
10
[Code] .........
View 3 Replies
View Related
Feb 10, 2014
I have 2 worksheets, 1 with a table sorted like this (in a row):
component | start date | end date | assigned to
Second worksheet with a table like a calendar with dates and people (dates in the columns and people in the rows), every component is assigned to each person by dates.
............ | 01/01 | 02/01 | 03/01
---------|----- --|-------|-------
person 1 | comp1 | comp1 | comp2
-------- |--------|-------|-------
person 2 | comp1 | comp1 | comp3
I want the assignment from worksheet 1 to worksheet 2 to be automatically. i will set the start and end date next to the component in the first table and assign it to a person/s and it will be automatically get filled in the second worksheet (the calendar) under the person/s and under the same dates as set in the first worksheet.
You can see a template here: [URL] ........
View 9 Replies
View Related
Mar 22, 2012
What I need to do is that I have electrical tools that get tested on a certain date in Column A. I need Column B to look at column A and determine and display in date format the next quarter when test is due.
Practical example: Extension cord got tested on 04/04/12. The next test needs to be conducted on 01/06/12. If the test is overdue and gets conducted on 02/06/12. The column A will change to 02/06/12 and column B should change to 01/09/12 as a result.
The formula I have got to work is
=DATE(YEAR(F2)+IF(MONTH(F2)>9,1,0),CHOOSE(MONTH(F2),4,4,4,7,7,7,10,10,10,1,1,1,),1)
but this makes the quarter dates as APRIL, JULY AND OCTOBER - they need to be MARCH, JUNE, SEPTEMBER
when I change the formula to
=DATE(YEAR(F2)+IF(MONTH(F2)>9,1,0),CHOOSE(MONTH(F2),3,3,3,6,6,6,9,9,9,1,1,1,),1)
The dates work BUT if the following test is undertaken on 01/03/12 the date in Column B does not change to 01/06/12. Why? How do I get it to work?
View 6 Replies
View Related
Jan 12, 2009
Is there anyway I would be able to display the current age of majority (19 here at home) in a cell or userform? This would have to be current each day the form or sheet was opened.
View 5 Replies
View Related
Jan 23, 2010
I have been trying to get this formula to work, and it appears it only works when it wants to and I cannot figure out why. What I'm trying to do: I have a spreadsheet with several columns of data, but I want the formula to read only two entire columns for a specific criteria for each column, then display the most recent date based off the criteria. I do not need to total up the criteria, just display the last date. This is what I have so far: =IF((AND(Sheet2!A:A=B2,Sheet2!B:B="Game: Counter Strike - Source")),MAX(Sheet2!C:C)).
I have this in a table, header as "last date" which is (C2), the B2 in my table would be where criteria would be typed in. So basically, my table should pull the last date from based off the criteria from raw data in sheet2, that is entered in to B2 on sheet1 and is "Game: Counter Strike - Source".
View 5 Replies
View Related
Feb 4, 2008
I currently have to display a large amount of data on a separate report. I have a master sheet which has columns and cells linked to another sheet, then a further sheet which has cells linked to the master sheet. The problem I am getting is when I link some of the date columns using a formula in the 3rd sheet, as this is looking at a cell that has another formula in on the other sheet it is displaying a date of 00/01/1900 if the value is zero, is there any easier way of getting the data to just display a blank as this is messing up the database it is being exported
View 9 Replies
View Related
Dec 18, 2008
I'm trying to get excel to display a quote, plus a cell's text/value... the problem I am having is that the format is a date, and it ends up showing something like 37896 instead of 11/25/2008 (those are examples, not actuals). Here's the
View 3 Replies
View Related
May 8, 2012
I have two separate worksheets:
I'm trying to find a formula that looks at Column A on both sheets and if they match enter in column D of the referral sheet the month they were seen but only if its a 1st contact (appt type on column D of contact sheet)
ABCDReferralIDReferral Date409383316 January 2012 Month / year of referral Month of first contact
485955005 March 2012January 2012539372005 September 2011March 2012385048229 February 2012
September 2011483172202 December 2011February 2012944156617 January 2012December 2011
393039322 February 2012January 2012
ABCDContact IDcontact Datemonth / year of contactAppointment Type
539372026 January 2012January 20121ST385048205 March 2012March 20121ST944156616 December 2011
December 2011F/UP222944119 December 2011December 2011F/UP344055529 December 2011
December 2011F/UP539372010 September 2011September 20111st
View 8 Replies
View Related
Dec 16, 2008
I maintain a class register in Excel to monitor student attendance. The first row shows the date of the class in the form dd-mm.
I need to identify all dates which fall on a Monday and thought that if I custom formatted a new row as "dddd" and enter the formula =DAY(cell ref) into the cells of this new row it would achieve this- I could easily spot the Mondays for the period under review.
What I'm finding, however, is that the formula seems to incorrectly state that 16th September 2008 is a Monday whereas it's actually a Tuesday- utterly bizarre!
I can get a fix simply by modifying the =DAY() formula by adding 1 to my formula [ie =DAY(A1)+1] but am wondering is this a "so called known issue" with Excel or has anyone else come across it? I have never previously come across this and consider myself to be an above average competency level user of the application.
View 6 Replies
View Related
Dec 12, 2011
I have cells on my spreadsheet which contain: =now() and they work as they should - displaying the date and time at the moment of entry. Unfortunately the cells update when other info is added at other locations on the sheet - is there a way to ensure they enter the current 'now' only at the point of initial entry - such that the data doesn't then alter?
View 3 Replies
View Related
Dec 23, 2011
I am working with a large spread sheet of people names and the courses that they have attended and what date they attended. The Sheet Identifies : ID No. Names, Courses, Date Attended, Due Date How can i remove all entries execpt for the last date for each qualification entered on each person?
ABCDEID. No.Name CourseDate AttendedDue Date22SmithFirst Aid2010/10/102011/10/1022SmithFirst Aid2011/10/102012/10/1021BobECDL2010/09/092011/09/0921BobECDL2011/10/102012/10/1022SmithECDL2010/08/082011/08/0822SmithECDL2011/09/092012/09/0920TerryFirst Aid2010/09/092011/09/0920TerryFirst Aid2011/10/102012/10/10
View 5 Replies
View Related
Jul 29, 2014
I have a spreadsheet containing 2 worksheets. First worksheet has my Students Details and the second spreadsheet has their Campus Locations.
What I need to create is a Formula that tells me "What CAMPUS was the student LOCATED at on the Course Completion Date?"
[URL]
what I'm trying to do is as follows:
John Completes his Course on the 25th July 2013, his Student ID is 450.
I need to go to the CAMPUS LOCATION spreadsheet and first search for Johns student ID (450) in column B. Because John is listed in B2,B3 and B4 excel needs to check date in Column E to see if his completion date was before the Date he started at Campus.
If it is then the Campus he was at will be the value of Column D on that line.
If it is not then it needs to search the next line until it satisfies the condition.
View 5 Replies
View Related
Jan 23, 2014
I have a database with a list of transactions with multiple fields, including:
Customer ID
Store ID
Item ID
Date
Price
I am wanting to add a calculated field that displays the first (or MIN) Date from the date column, looking at all of the orders where the customer ID, store ID, item ID and price are the same.
In the attached file I have shown what I currently have, as well as a solution using a helper column, and formulas I would use if I wasn't using powerpivot.
View 2 Replies
View Related
Dec 1, 2009
I have a workbook full of a bunch of sheets with the same formatting. Each sheet has data for a 2 week period.
I am making a “master” type sheet and I’m hoping to be able to pull some data from each sheet.
On each sheet:
Row 1 has dates starting on E1 and ending on AF1 (several blank/combined columns)
Row 22 has the data I want to pull, and it is one column to the right of the date. So, F22 corresponds with E1…
Ex.
One sheet looks like this:
E1 = 8/24/09
AF1 = 9/6/09
I got single amounts to work by using:
=IF((MONTH(E1)=8),F22," ")
However, I’m hoping to find a formula that would look at the entire two week period and sum the row 22 amounts for each day that falls within a certain month.
I tried:
=IF((MONTH(E1:AG1)=8),F22:AG22," ")
Which comes back false since not all days in the two week period fall within August (8).
Does anyone have any ideas for a formula that would be easily changeable, and that would pull & sum row 22 data for each row 1 date that falls within a certain month?
View 9 Replies
View Related
Dec 20, 2011
Using MS Query in Excel, I've created a simple query that pulls its records from an SQL dbase. Here's the statement:
SELECT uvVisit.FacilityListName, uvVisit.DoctorListName, uvVisit.Date, uvVisit.PatientVisitId, uvVisit.PatientLast, uvVisit.PatientFirst
FROM CPS.dbo.uvVisit uvVisit
WHERE (uvVisit.Date Between ? And ?)
ORDER BY uvVisit.FacilityListName
The query runs fine and prompts the user to enter beginning & ending date ranges for the visit date when executed. So far...so good...but, this requires me to manually insert a line in Excel above the 1st record and type in something like: "For Date Range: MM/DD/YYY - MM/DD/YY" to denote the date range that the qualified records fall into (something the user wants to see).
However, I'd like to find a way automatically preface and display in the report's output (perhaps as the 1st line of the report in Excel??) something similar to what I'm already typing, and have it pull the beginning and ending MM/DD/YY values from those supplied by the user in the parameter.
View 3 Replies
View Related
Jun 25, 2014
help.jpg
I'm new to VB. I am writing a code to insert a value in to the textbox depending on the focus.
For example if i have kept the focus on textbox1 and I click the particular date on calendar, then it should print the date on textbox1 and vice versa.
View 4 Replies
View Related
Jun 1, 2009
I am trying to import a several files depending on date. The first part of the name of the files that I am importing are always the same, the end changes as the date changes.
I have a user form that comes up and I pick the dates that I am looking at.
What would be the code to import those depending on the date that I pick?
When my UF comes up, I pick whatever dates, I need to import all the files in between those dates.
View 11 Replies
View Related
Jun 12, 2009
I have a truck log. Every time some comes in to take a truck they must log the truck out with the millage. After a week, I am supposed to find out how many miles my trucks ran for that week.
What would be the code to....
In K column, calculate the milage that is in row F; which is A3 - A2 and so on down. But it would have to skip a line every time the truck number changes (in column C) it would skip that line. It would go until a blank cell.
Does anyone have any ideas how to do that?
View 10 Replies
View Related
Oct 30, 2009
I have a WB with 31 sheets, each dated 1st to 31st and currently hidden.
I have been trying to write a VBA code that would open a sheet based on todays date... so if today is the 2nd of Nov it would unhide the sheet 2nd, I would also like it to check if a sheet dated 1st is visable, and if it is, hide it.
The date for the sheet to hide would I think need to be relative... but I don't know how to get it right.
View 9 Replies
View Related
Aug 12, 2009
I need to complete the 'cost field' in Table A, from Table B, but the 'cost' value must be selected from Table B based on it's ' date completed', as per my example attached...
View 9 Replies
View Related
Jan 8, 2008
i have a excel sheet which i use to schedule my service team. Basically i have horizontally the dates ((a colum for every day of the year) and vertically the guys names (about 25 rows).
I would like to add a function so i can push a button and i automatically print the past, current and next week of my shedule on one page.
In addition it should print my usage chart which is on a different work sheet in the same file.
View 9 Replies
View Related
