Overclock.net › Forums › Software, Programming and Coding › Coding and Programming › Formating in Excel using formulas?
New Posts  All Forums:Forum Nav:

Formating in Excel using formulas?

post #1 of 5
8/3/12 at 2:19pm
Thread Starter 
  • Joined: Jun 2009
  • Location: Chicago Suburbs, Illinois
  • Posts: 2,982
  • Rep: 147 (Unique: 130)
  • Reviews: 2
  • Trader Rating: 4
  • Folding Team Rank: 132
  •  
    Name Team Rank Points / May Work Units / May
    scifiguy 132 20,261,947 / 431,703 15,806 / 82
       
  • Select All Posts By This User
Ok, so I do not know that much about excel formulas or anything. Currently I have a spreadsheet with dates on it. When the date comes within 5 days, I manually change the color of the row (but only columns A-F) to yellow, and when the date passes I change it to red.

Is there a formula that I can insert into the spreadsheet that would have the color change automatically?

Thanks.
Black Vengence
(15 items)
 
HAF Dust Magnet
(18 items)
  		 
CPUMotherboardGraphicsGraphics
i7 3930K ASUS Rampage IV Extreme EVGA GTX 680 SC+ Signature EVGA GTX 470 
RAMHard DriveHard DriveCooling
Corsair Vengence 16GB Intel 520 SSD 240GB WD Black 1TB Corsair H100 
OSMonitorKeyboardPower
Windows 7 ASUS V236H Logitch G15 Corsair AX1200 
CaseMouseAudio
Cooler Master HAF X Logitech G500 Creative X-FI Titanium 
View all
CPUMotherboardGraphicsGraphics
i7 930 EVGA X58 SLI EVGA GTX 470 EVGA GTX 470 
RAMHard DriveHard DriveOptical Drive
6GB G.Skill PC-12800 Phoenix Pro SSD Seagate Baracude Pioneer DVD-RW Burner 
Optical DriveCoolingOSMonitor
Asus BW-12B1ST Blu-ray Writer Corsair H100 Windows 7 Pro x64 Asus VH236H 
KeyboardPowerCaseMouse
Logitech G15 Gaming Keyboard Corsair 750TX HAF 932 Logitech G500 
Audio
Creative X-FI Titanium 
View all
  hide details  
Reply
Black Vengence
(15 items)
 
HAF Dust Magnet
(18 items)
  		 
CPUMotherboardGraphicsGraphics
i7 3930K ASUS Rampage IV Extreme EVGA GTX 680 SC+ Signature EVGA GTX 470 
RAMHard DriveHard DriveCooling
Corsair Vengence 16GB Intel 520 SSD 240GB WD Black 1TB Corsair H100 
OSMonitorKeyboardPower
Windows 7 ASUS V236H Logitch G15 Corsair AX1200 
CaseMouseAudio
Cooler Master HAF X Logitech G500 Creative X-FI Titanium 
View all
CPUMotherboardGraphicsGraphics
i7 930 EVGA X58 SLI EVGA GTX 470 EVGA GTX 470 
RAMHard DriveHard DriveOptical Drive
6GB G.Skill PC-12800 Phoenix Pro SSD Seagate Baracude Pioneer DVD-RW Burner 
Optical DriveCoolingOSMonitor
Asus BW-12B1ST Blu-ray Writer Corsair H100 Windows 7 Pro x64 Asus VH236H 
KeyboardPowerCaseMouse
Logitech G15 Gaming Keyboard Corsair 750TX HAF 932 Logitech G500 
Audio
Creative X-FI Titanium 
View all
  hide details  
Reply
post #2 of 5
8/3/12 at 2:53pm
One way to do it would be to use conditional formatting found in the home ribbon. You select the columns you want the formatting to apply to, in your case and since you mention columns A:F those would be the columns you select.

You then need to click on the conditional formatting command and you will need to insert two rules, so you click on insert new rule. Select use a formula to determine which cells to format and then input the formula below exactly as it is.
Code:
=AND(A1<=TODAY()-1,A1>=TODAY()-5)

The formula above will be satisfied on dates that are up to five days previous to the current day. You then need to click on format and choose how you want the cells that satisfy the above condition to appear, probably changing the fill of the cells. To format today's date you need to insert a second rule using the formula below.
Code:
=A1=TODAY()

Those two rules will allow you to do what you requested and will change the cells' format as the date changes.
Sandy met Kepler !
(13 items)
 
Portable Powah !
(13 items)
  		 
CPUMotherboardGraphicsRAM
Intel Core i5-2500K[4.5 ghz 1.25V] ASUS Sabertooth P67 (rev. B3) Gigabyte Windforce 3x GTX680  Corsair Vengeance 8GB DDR3-1600 Kit 
Hard DriveOSMonitorKeyboard
OCZ Vertex 4 128 GB Boot/1 Tb Gb WD Raid 0 Windows 7 Ultimate x64 Yamakasi Catleap 27" Q270 LED SE Logitech G15 
PowerCaseMouse
CoolerMaster Silent Pro M700 CoolerMaster HAF 922 Roccat Kone + 
View all
CPUGraphicsRAMHard Drive
Intel Core i5 2430M GeForce GT 540M DDR3 4 GB Seagate Momentus XT 500 GB 
OSMonitor
Windows 7 Home 64bit 15.6" 1366x768 
View all
  hide details  
Reply
Sandy met Kepler !
(13 items)
 
Portable Powah !
(13 items)
  		 
CPUMotherboardGraphicsRAM
Intel Core i5-2500K[4.5 ghz 1.25V] ASUS Sabertooth P67 (rev. B3) Gigabyte Windforce 3x GTX680  Corsair Vengeance 8GB DDR3-1600 Kit 
Hard DriveOSMonitorKeyboard
OCZ Vertex 4 128 GB Boot/1 Tb Gb WD Raid 0 Windows 7 Ultimate x64 Yamakasi Catleap 27" Q270 LED SE Logitech G15 
PowerCaseMouse
CoolerMaster Silent Pro M700 CoolerMaster HAF 922 Roccat Kone + 
View all
CPUGraphicsRAMHard Drive
Intel Core i5 2430M GeForce GT 540M DDR3 4 GB Seagate Momentus XT 500 GB 
OSMonitor
Windows 7 Home 64bit 15.6" 1366x768 
View all
  hide details  
Reply
post #3 of 5
8/3/12 at 3:26pm
Thread Starter 
  • Joined: Jun 2009
  • Location: Chicago Suburbs, Illinois
  • Posts: 2,982
  • Rep: 147 (Unique: 130)
  • Reviews: 2
  • Trader Rating: 4
  • Folding Team Rank: 132
  •  
    Name Team Rank Points / May Work Units / May
    scifiguy 132 20,261,947 / 431,703 15,806 / 82
       
  • Select All Posts By This User
Quote:
Originally Posted by geovas77 View Post

One way to do it would be to use conditional formatting found in the home ribbon. You select the columns you want the formatting to apply to, in your case and since you mention columns A:F those would be the columns you select.
You then need to click on the conditional formatting command and you will need to insert two rules, so you click on insert new rule. Select use a formula to determine which cells to format and then input the formula below exactly as it is.
Code:
=AND(A1<=TODAY()-1,A1>=TODAY()-5)
The formula above will be satisfied on dates that are up to five days previous to the current day. You then need to click on format and choose how you want the cells that satisfy the above condition to appear, probably changing the fill of the cells. To format today's date you need to insert a second rule using the formula below.
Code:
=A1=TODAY()
Those two rules will allow you to do what you requested and will change the cells' format as the date changes.

Thanks, now I just realized I have a 3rd color that I need. If the value of a cell a particular word in any part of the cell, i need it to change to a different color. How do I do that and will it overide the other 2 rules?

I am tracking paperwork, so if I have already received the paperwork I will enter in rec'd in column E, but there may be other words in there too because that is a cell I use for comments.
Black Vengence
(15 items)
 
HAF Dust Magnet
(18 items)
  		 
CPUMotherboardGraphicsGraphics
i7 3930K ASUS Rampage IV Extreme EVGA GTX 680 SC+ Signature EVGA GTX 470 
RAMHard DriveHard DriveCooling
Corsair Vengence 16GB Intel 520 SSD 240GB WD Black 1TB Corsair H100 
OSMonitorKeyboardPower
Windows 7 ASUS V236H Logitch G15 Corsair AX1200 
CaseMouseAudio
Cooler Master HAF X Logitech G500 Creative X-FI Titanium 
View all
CPUMotherboardGraphicsGraphics
i7 930 EVGA X58 SLI EVGA GTX 470 EVGA GTX 470 
RAMHard DriveHard DriveOptical Drive
6GB G.Skill PC-12800 Phoenix Pro SSD Seagate Baracude Pioneer DVD-RW Burner 
Optical DriveCoolingOSMonitor
Asus BW-12B1ST Blu-ray Writer Corsair H100 Windows 7 Pro x64 Asus VH236H 
KeyboardPowerCaseMouse
Logitech G15 Gaming Keyboard Corsair 750TX HAF 932 Logitech G500 
Audio
Creative X-FI Titanium 
View all
  hide details  
Reply
Black Vengence
(15 items)
 
HAF Dust Magnet
(18 items)
  		 
CPUMotherboardGraphicsGraphics
i7 3930K ASUS Rampage IV Extreme EVGA GTX 680 SC+ Signature EVGA GTX 470 
RAMHard DriveHard DriveCooling
Corsair Vengence 16GB Intel 520 SSD 240GB WD Black 1TB Corsair H100 
OSMonitorKeyboardPower
Windows 7 ASUS V236H Logitch G15 Corsair AX1200 
CaseMouseAudio
Cooler Master HAF X Logitech G500 Creative X-FI Titanium 
View all
CPUMotherboardGraphicsGraphics
i7 930 EVGA X58 SLI EVGA GTX 470 EVGA GTX 470 
RAMHard DriveHard DriveOptical Drive
6GB G.Skill PC-12800 Phoenix Pro SSD Seagate Baracude Pioneer DVD-RW Burner 
Optical DriveCoolingOSMonitor
Asus BW-12B1ST Blu-ray Writer Corsair H100 Windows 7 Pro x64 Asus VH236H 
KeyboardPowerCaseMouse
Logitech G15 Gaming Keyboard Corsair 750TX HAF 932 Logitech G500 
Audio
Creative X-FI Titanium 
View all
  hide details  
Reply
post #4 of 5
8/3/12 at 3:32pm
Quote:
Originally Posted by muels7 View Post

Thanks, now I just realized I have a 3rd color that I need. If the value of a cell a particular word in any part of the cell, i need it to change to a different color. How do I do that and will it overide the other 2 rules?
I am tracking paperwork, so if I have already received the paperwork I will enter in rec'd in column E, but there may be other words in there too because that is a cell I use for comments.

You can do this with conditional formatting as well.

When you create the rule, set it at the top and make sure you have it stop if true.

Hope this helps,

Rich
Ichiban
(17 items)
 
Niban
(20 items)
  		 
CPUMotherboardGraphicsRAM
i7-2600K ASUS P8Z68-V Pro ASUS EAH 6950 2GB DCII Corsair Vengeance PC3-12800 1600mHz 2x4GB 
Hard DriveHard DriveOptical DriveOptical Drive
Intel 320 series Western Digital Caviar Black LG Super Multi Blu Ray Burner LG Super Multi DVD Burner 
CoolingOSMonitorKeyboard
Cooler Master Hyper 212 plus Win 7 Pro 64 bit 3 x Dell U2311H/Dell 2209WA Logitech K800 
PowerCaseMouseMouse Pad
Corsair AX850 Corsair Obsidian 650D Logitech Performance MX Core XL 
Other
Scythe Kaze Master Pro Fan Controller 
View all
CPUMotherboardGraphicsRAM
Intel i7 3930K ASUS P9X79 Deluxe ASUS HD7970 DCII Corsair Vengeance Low Profile 16GB 
Hard DriveHard DriveOptical DriveOptical Drive
Intel 520 Series Western Digital Caviar Black LG Super Multi BluRay Burner LG Super Multi DVD Burner 
CoolingOSMonitorMonitor
Noctua NH-D14 SE2011 Windows 7 Pro 64-bit Dell U2410 Dell U2410 
MonitorKeyboardPowerCase
Dell U2410 Logitech K800 Illuminated Wireless Corsair AX850 Corsair Obsidian 550D 
MouseMouse PadAudioOther
Logitech Performance Mouse MX Core XL Bose Companion 20 Multimedia Speakers Scythe Kaze Master Pro Fan Controller 
View all
  hide details  
Reply
Ichiban
(17 items)
 
Niban
(20 items)
  		 
CPUMotherboardGraphicsRAM
i7-2600K ASUS P8Z68-V Pro ASUS EAH 6950 2GB DCII Corsair Vengeance PC3-12800 1600mHz 2x4GB 
Hard DriveHard DriveOptical DriveOptical Drive
Intel 320 series Western Digital Caviar Black LG Super Multi Blu Ray Burner LG Super Multi DVD Burner 
CoolingOSMonitorKeyboard
Cooler Master Hyper 212 plus Win 7 Pro 64 bit 3 x Dell U2311H/Dell 2209WA Logitech K800 
PowerCaseMouseMouse Pad
Corsair AX850 Corsair Obsidian 650D Logitech Performance MX Core XL 
Other
Scythe Kaze Master Pro Fan Controller 
View all
CPUMotherboardGraphicsRAM
Intel i7 3930K ASUS P9X79 Deluxe ASUS HD7970 DCII Corsair Vengeance Low Profile 16GB 
Hard DriveHard DriveOptical DriveOptical Drive
Intel 520 Series Western Digital Caviar Black LG Super Multi BluRay Burner LG Super Multi DVD Burner 
CoolingOSMonitorMonitor
Noctua NH-D14 SE2011 Windows 7 Pro 64-bit Dell U2410 Dell U2410 
MonitorKeyboardPowerCase
Dell U2410 Logitech K800 Illuminated Wireless Corsair AX850 Corsair Obsidian 550D 
MouseMouse PadAudioOther
Logitech Performance Mouse MX Core XL Bose Companion 20 Multimedia Speakers Scythe Kaze Master Pro Fan Controller 
View all
  hide details  
Reply
post #5 of 5
8/3/12 at 11:43pm
Quote:
Originally Posted by muels7 View Post

Thanks, now I just realized I have a 3rd color that I need. If the value of a cell a particular word in any part of the cell, i need it to change to a different color. How do I do that and will it overide the other 2 rules?
I am tracking paperwork, so if I have already received the paperwork I will enter in rec'd in column E, but there may be other words in there too because that is a cell I use for comments.

Assuming that you only use column E for comments then you would select column E and enter the following formula as a new rule. Be aware that the formula looks for exacts so if you have REC'D instead of rec'd it will not work.
Code:
=NOT(ISERR(FIND("rec'd",E1,1)))
Sandy met Kepler !
(13 items)
 
Portable Powah !
(13 items)
  		 
CPUMotherboardGraphicsRAM
Intel Core i5-2500K[4.5 ghz 1.25V] ASUS Sabertooth P67 (rev. B3) Gigabyte Windforce 3x GTX680  Corsair Vengeance 8GB DDR3-1600 Kit 
Hard DriveOSMonitorKeyboard
OCZ Vertex 4 128 GB Boot/1 Tb Gb WD Raid 0 Windows 7 Ultimate x64 Yamakasi Catleap 27" Q270 LED SE Logitech G15 
PowerCaseMouse
CoolerMaster Silent Pro M700 CoolerMaster HAF 922 Roccat Kone + 
View all
CPUGraphicsRAMHard Drive
Intel Core i5 2430M GeForce GT 540M DDR3 4 GB Seagate Momentus XT 500 GB 
OSMonitor
Windows 7 Home 64bit 15.6" 1366x768 
View all
  hide details  
Reply
Sandy met Kepler !
(13 items)
 
Portable Powah !
(13 items)
  		 
CPUMotherboardGraphicsRAM
Intel Core i5-2500K[4.5 ghz 1.25V] ASUS Sabertooth P67 (rev. B3) Gigabyte Windforce 3x GTX680  Corsair Vengeance 8GB DDR3-1600 Kit 
Hard DriveOSMonitorKeyboard
OCZ Vertex 4 128 GB Boot/1 Tb Gb WD Raid 0 Windows 7 Ultimate x64 Yamakasi Catleap 27" Q270 LED SE Logitech G15 
PowerCaseMouse
CoolerMaster Silent Pro M700 CoolerMaster HAF 922 Roccat Kone + 
View all
CPUGraphicsRAMHard Drive
Intel Core i5 2430M GeForce GT 540M DDR3 4 GB Seagate Momentus XT 500 GB 
OSMonitor
Windows 7 Home 64bit 15.6" 1366x768 
View all
  hide details  
Reply
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: Coding and Programming
Overclock.net › Forums › Software, Programming and Coding › Coding and Programming › Formating in Excel using formulas?