i need a formula that does the following:
if the date is two months under the date it needs to be checked it goes yellow
if its over two months before it needs checked it goes green
if its passed the date it needs to be checked by it turns red
I would like to change the colour of a userform both the background colour and the font colour. However, in the colour property box I am met with &H00C0C0C0& (which is Grey). I can select some other colours but not the one I would like to use.
I have tried inserting RGB(194,214,154) however I get the error 'Invaild proprty value'
i am creating a spreadsheet to account for work being done on a team. At the moment the sheet adds up the average time that the whole team is taking to do a task,and it also adds up the time each person is taking.
so for instance: KL take 100 minutes to do the task. but the average time across the team is 90 minutes per task.
i have my list of staff with their times in column E. so what i want is if their time is higher than the average then the box turns red, if it is lower then green etc.
(the average figure is got by summing everyones totals in box B22.
I have a conditional formula which populates a string depending on the value selected by user.
E.G of formula is:- =IF($B$1=0,"CONTACT PERSON - OPTIONAL",IF($B$1=5,"CONTACT PERSON - MANDATORY",""))
Where the result is "CONTACT PERSON - MANDATORY" then I want the word MANDATORY (not the whole string) to be displayed in RED.
I have a conditional formula which gives a value based on what the User specifies.
Example of formula is:
=IF($B$1=0,"CONTACT PERSON - OPTIONAL",IF($B$1=5,"CONTACT PERSON (FINANCE ONLY) - MANDATORY",""))
If the result of the string is " CONTACT PERSON (FINANCE ONLY) - MANDATORY " then
- word MANDATORY (not the whole string) should be in RED
- words (FINANCE ONLY) should be in BLUE
- remaining string should be left in BLANK
Not intending to use VBA. Is this possible?
I have a spreadsheet which reports across different categories, but there's a lot of info. So simplify this, I've written a simple macro that changes the series range in an existing chart, so top level and by category. This works well apart from I'm using excel 2007, and some people aren't, so the default colours look fine on my version, but change to some awful colours on anyone with an earlier version of excel. I need a way of changing the colours to standard ones depending on which macro is being run.
I have tried running the following, but I get an error, basically saying the series hasn't been selected.
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.SeriesCollection(3).Select
ActiveChart.SeriesCollection(3).Interior.Color = RGB(255, 0, 0)
Is it possible to have a listbox with each line with a different colour?
View 2 Replies View Related I'm sure this will be an easy query.
These are marks scored by 5 people in 10 exams
Name of the student
MARKS
MARKS
MARKS
MARKS
MARKS
MARKS
MARKS
MARKS
MARKS
MARKS[code]....
Range
A1 to K6
In each row top 5 MARKS should be filled with BLUE,VIOLET,YELLOW,RED AND PINK
If its not possible. Top 3 with dark colour, remaining 2 with light colour using conditional formatting
What I need to do is have a cell that will be say yellow until there is information put into this cell. The information could be in the format of text or numbers. The information would not always be the same so it would need to be yellow when there is no information in the cell and another colour or white when there is information in the cell.
View 6 Replies View Relatedexcel.JPG
Is there any way to Merge the cell and coloured automatically by Date range same like above picture. In the Image:
"MERIAN FROM 1/1/2014 12:01:00 AM TO 1/2/2014 10:15:00 AM" is Merged and Coloured according to Date January 1 and Time 00
Same also...
"SILVER FERN FROM 1/2/2014 1:20:00 PM TO 1/3/2014 11:20:00 AM"
I have a cell which has multiple references including some text and a date. When they are all added together the date comes up with the 6 numbers not a date.
[Code]......
The TR!A11 is the date. Is there code to put in front of it to make it format as a date??
The idea came about after wanting to show a 'gold' 'silver' and 'bronze' background colour, but having to plump for yellow, grey and orange looked a little crap :f
If not, can a workaround be created by using code and a picture or 2?
I work with several dates in Excel 2007. What forumula can I put in a blank cell that would change the below "Start Date" to 3/1/12 if the date displayed is less than 3/1/12? Also, if a cell in the "Start Date" column is blank, how can I populate a blank cell with a specified date value such as 3/31/12?
ShipPositionStart DateEnd DateBoard DaysBlankMASTER2/15/2012-40953BlankCH.MATE1/22/20123/12/201251Blank2ND MATE2/29/20123/31/201232Blank3RD MATE3/1/2012-40968BlankBOSUN3/5/20123/31/201227
I am evaluating a wellness programme at work. One of the things we measure is BMI (Body Mass Index).
What I’m wanting to do is to categorise the BMI values into “flags”. For example someone who has a BMI of >35 could be red, someone with a BMI of 25-35 could be yellow, and <24 green.
What i’m wondering if excel can do is;
If I write the BMI value one cell can I get excel to automatically fill in another cell in either the colour or the name of the colour?
I'm creating a project calendar in Excel and for each task I have a Start Date (A) and End Date (B) in adjacent cells. To the right are corresponding date cells like a timeline set up with 52 cells representing weeks of the year. I.e Week 1 (C) starts at 02 Jul 12, Week 2 (D) starts at 09 jul 12 etc. up to 24 Jun 14 (BF).
Start Date
End date
02-Jul-12
09-Jul-12
[Code]......
When a start date and end date are entered in the respective columns, I need to have the color start on the cell for that range in the timeline and that color should carry out to the end date cell in the timeline. A bit like a Gantt chart but not to that detail.
I have a long list of dates that are fomatted as 3-12-13 that I want to format under custom date as d-mmm-yy : 12-Mar-13. I tried to do this but it does not change. I tried to change it to a number and that didn't work either.
View 2 Replies View RelatedIf a1,a21,a41 have a value greater than 0, I wish to stamp cell b1,b21,b41 and so on with todays date, but without the stamped value changing/advancing tomorrow. At the moment cells b1,b21,b41 are copied down as follows. =if(a1>0,TODAY(),"") This works fine, but the date stamp of course changes tomorrow. I can not apply code to the whole b column as cells b2:b20, b22:40 etc have other non-date format data to which the code does not need to apply - the todays date value cell occurs every 20 cells.
View 6 Replies View RelatedI need an IF formula, that states if a date is more than a month after the date in the cell then it is timely and if it isn't then it is not timely. For example the date in A1 is 12/11/05, if the cell is A2 is 13/12/05 (or any date after that) then A3 should read Not Timely, if A2 is 12/12/05 (or any date before that, including a date before the date in A1) then A3 should read Timely. Is there any way to do this?? At the moment I am having to go through manually and put in either Timely or Not Timely.
View 2 Replies View RelatedI have data in these two formats 7/13/2014 9:24:14 AM and 07/12/2014 13:02. I want them in Am - PM style. I tried date , there is no format which suits my requirement. The first format in excel is general and other is custom under dd/mm/yyyy hh:mm . I have 23k rows, which I cannot afford to change manually.
View 3 Replies View RelatedI've got dates and times in the format of a text string. each string is always formatted in the exact same way to identify the data and time. As such:
Wed Aug 12 16:39:52 BST 2009
Mon Aug 10 14:39:03 BST 2009
Sat Aug 08 18:01:34 BST 2009
And I want to change them to a date formate that excel can understand, so i can search by month, or by date range. The main crux of the question is how to change that string into a excel understandable date though.
If you want to know, this is for my mobile phone bill. I have excel set up to give me a summery of how many texts I've sent, minuets I've used/have remaining. but i want to filter them by month, so I can see how my usage changes.
FYI I put up this question on another forum, however I'm asking it again here b/c I'm not really getting an informative answer there and this project is due soon.
[url]
I'm working on a file right now where dates are in the format "06/01/09" meaning 6 Jan 2009. Or more specifically thats what the data is supposed to mean, however when I change it to word form it appears as June 1 2009. This means dates such as "27/01/09" dont get translated.
How can I fix this? I've tried changing the date format, and I've changed the global settings, yet this isnt working.
In case its relevant, before I pasted this data into the current spreadsheet my global settings were set at month day year, which is what I prefer. But the rest of the office works on DMY so I'm switching to that.
How to have VBA return the value of the following month in mm format (i.e. 08 for August, not 8), based on today's date. As such, today it being the 7th month, I wish to return the value 08 (the 8th month).
I need the URL of
[Code] .....
To essentailly be
[Code] ......
but the way in which I have posted it above does not work!
Also, I need to define it such that as the month in question (in this example 08) will rename the subsequent part of the URL (here, August). So for example, next month (August), the URL should automatically become
[Code] .....
How can I track date of every last change made in certain cells so my boss can monitor if I am keep working on a project?
View 9 Replies View RelatedI have an entire column with the date reading in this order:
7/4/19
The actual date should be April 19th, 2007, however, Excel is reading it as
July 4th, 2019.
How do I correct it?
I have 2 Excel Files. In the main file I have a macro that copy/pastes data from the secondary file. The secondary file is a daily file that gets its file name changed every day (eg. 14OCT09.xls).
The last piece of the Macro goes back to the secondary file and ends with:
Windows("14OCT09.xls").activate
Range ("A1").Select
Now tomorrow, I have to manually edit the Macro and change the code to 15OCT09.xls before I run the Macro. Is there a way to automatically change this date without having to manually edit it on a daily basis?
I have a form to imput a date but the problem is that it changes the computer date aswell. Is there another code i can use? Im using CDate code.
Private Sub CommandButtonProceed_Click()
Sheet2.Cells(2, 57) = TextBoxInitials
Cells(1, 2) = TextBoxSiteName
Cells(2, 2) = CDate(TextBoxDate)
Date = Cells(2, 2)
Sheet2.Cells(3, 28) = TextBoxPlant1
Sheet2.Cells(3, 31) = TextBoxPlant2
Sheet2.Cells(3, 34) = TextBoxPlant3
Sheet2.Cells(3, 37) = TextBoxPlant4
Sheet2.Cells(3, 40) = TextBoxPlant5
Sheet2.Cells(3, 43) = TextBoxPlant6
Sheet2.Cells(5, 27) = ExtractElement(Date, 3, "/")
Unload Me
DataImput.Show
End Sub
way to change the date layout at all.
the problem is that i have a user form with a text box in it to put in the date i.e 08/02/07 but when i click the button to add it to the worksheet that has the database it comes out as 02/08/07 is any way to change or will i just have to put the month first rather then the day
I got a simple spreadsheet that I made for personal use. I want to be able to tell when certain boxes were last modified.
For example..
A1: any number
B1: date that A1 was last modified
I want it so each time I change the value in column A1 it will change the date in B1 to the date it was changed (ie, today).
I also need to tally up all the numbers listed in the A column to give a total of all the numbers in a cell below them.
When I tried to import data in a CSV file, I ran into a couple of issues when trying to change the date format from MM/DD/YYY to other date format. I've already tried a couple things to no avail:
1. Use text to column functions and change the format from text to MMDDY
2. Use format cell function and change the date format
3. Use the text function (text(Cell,"MM-YY"))
Interestingly when I click on the cell and put my cursor and press backspace, the format will change. It seems like there's an extra space, but using the trim function does not work. I can't do this to thousands of data manually.
I have a list with several thousand dates listed like:
19931014
19900724
19910701
19911209
That is, yyyymmdd.
I would like to list it as yymm only. Is there a way to convert it in Excel? I have for a long time been copying and pasting in to Word then doing a search and replace of the first and last two digits, which results in the yymm format I am looking for. A better way would be nice.