Conditional Formatting by Date

Howdy All,


I have a budget workbook which contains a worksheet for every month and I 
need to format certain cells to show a value from a specific date forward.

Example:

Car payment needs to be applied on the 15th and remain there when the month 
rolls over. So on the fifteenth of May, I need cell C5 to display $350.00, 
but I don't want May's C5 to reset back to 0 when the date rollovers over to 
June 1st.

I hope I am clear on this.

Thanks for you time and attention,
Brian



0
wolfmanx (4)
4/30/2006 6:17:09 PM
excel 39879 articles. 2 followers. Follow

4 Replies
397 Views

Similar Articles

[PageSpeed] 27

hi!

in A1: =TODAY()

and

in C5: =(IF(AND(MONTH(A1)=5,DAY(A1)=15),TEXT(350,"$000"),""))

-via135


Wolfman Wrote:
> Howdy All,
> 
> 
> I have a budget workbook which contains a worksheet for every month an
> I
> need to format certain cells to show a value from a specific dat
> forward.
> 
> Example:
> 
> Car payment needs to be applied on the 15th and remain there when th
> month
> rolls over. So on the fifteenth of May, I need cell C5 to displa
> $350.00,
> but I don't want May's C5 to reset back to 0 when the date rollover
> over to
> June 1st.
> 
> I hope I am clear on this.
> 
> Thanks for you time and attention,
> Bria

--
via13
-----------------------------------------------------------------------
via135's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2672
View this thread: http://www.excelforum.com/showthread.php?threadid=53760

0
4/30/2006 8:12:33 PM
Thanks Via, but if the date is anything BUT the 15th, the cell displays 
nothing :(


"via135" <via135.273fyy_1146428101.2121@excelforum-nospam.com> wrote in 
message news:via135.273fyy_1146428101.2121@excelforum-nospam.com...
>
> hi!
>
> in A1: =TODAY()
>
> and
>
> in C5: =(IF(AND(MONTH(A1)=5,DAY(A1)=15),TEXT(350,"$000"),""))
>
> -via135
>
>
> Wolfman Wrote:
>> Howdy All,
>>
>>
>> I have a budget workbook which contains a worksheet for every month and
>> I
>> need to format certain cells to show a value from a specific date
>> forward.
>>
>> Example:
>>
>> Car payment needs to be applied on the 15th and remain there when the
>> month
>> rolls over. So on the fifteenth of May, I need cell C5 to display
>> $350.00,
>> but I don't want May's C5 to reset back to 0 when the date rollovers
>> over to
>> June 1st.
>>
>> I hope I am clear on this.
>>
>> Thanks for you time and attention,
>> Brian
>
>
> -- 
> via135
> ------------------------------------------------------------------------
> via135's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=26725
> View this thread: http://www.excelforum.com/showthread.php?threadid=537605
> 


0
wolfmanx (4)
5/1/2006 12:43:24 AM
If you use sheet names like Apr-200, May-2006, you could use this

=IF(TODAY()>=DATEVALUE("15-"&MID(CELL("filename",A1),FIND("]",CELL("filename
",A1))+1,255)),350,"")

-- 
 HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Wolfman" <wolfmanx@charter.net> wrote in message
news:uu7wIhLbGHA.3524@TK2MSFTNGP04.phx.gbl...
> Thanks Via, but if the date is anything BUT the 15th, the cell displays
> nothing :(
>
>
> "via135" <via135.273fyy_1146428101.2121@excelforum-nospam.com> wrote in
> message news:via135.273fyy_1146428101.2121@excelforum-nospam.com...
> >
> > hi!
> >
> > in A1: =TODAY()
> >
> > and
> >
> > in C5: =(IF(AND(MONTH(A1)=5,DAY(A1)=15),TEXT(350,"$000"),""))
> >
> > -via135
> >
> >
> > Wolfman Wrote:
> >> Howdy All,
> >>
> >>
> >> I have a budget workbook which contains a worksheet for every month and
> >> I
> >> need to format certain cells to show a value from a specific date
> >> forward.
> >>
> >> Example:
> >>
> >> Car payment needs to be applied on the 15th and remain there when the
> >> month
> >> rolls over. So on the fifteenth of May, I need cell C5 to display
> >> $350.00,
> >> but I don't want May's C5 to reset back to 0 when the date rollovers
> >> over to
> >> June 1st.
> >>
> >> I hope I am clear on this.
> >>
> >> Thanks for you time and attention,
> >> Brian
> >
> >
> > -- 
> > via135
> > ------------------------------------------------------------------------
> > via135's Profile:
> > http://www.excelforum.com/member.php?action=getinfo&userid=26725
> > View this thread:
http://www.excelforum.com/showthread.php?threadid=537605
> >
>
>


0
bob.phillips1 (6510)
5/1/2006 11:10:03 PM
Thanks Bob!

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message 
news:%23Xqs7RXbGHA.4716@TK2MSFTNGP03.phx.gbl...
> If you use sheet names like Apr-200, May-2006, you could use this
>
> =IF(TODAY()>=DATEVALUE("15-"&MID(CELL("filename",A1),FIND("]",CELL("filename
> ",A1))+1,255)),350,"")
>
> -- 
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Wolfman" <wolfmanx@charter.net> wrote in message
> news:uu7wIhLbGHA.3524@TK2MSFTNGP04.phx.gbl...
>> Thanks Via, but if the date is anything BUT the 15th, the cell displays
>> nothing :(
>>
>>
>> "via135" <via135.273fyy_1146428101.2121@excelforum-nospam.com> wrote in
>> message news:via135.273fyy_1146428101.2121@excelforum-nospam.com...
>> >
>> > hi!
>> >
>> > in A1: =TODAY()
>> >
>> > and
>> >
>> > in C5: =(IF(AND(MONTH(A1)=5,DAY(A1)=15),TEXT(350,"$000"),""))
>> >
>> > -via135
>> >
>> >
>> > Wolfman Wrote:
>> >> Howdy All,
>> >>
>> >>
>> >> I have a budget workbook which contains a worksheet for every month 
>> >> and
>> >> I
>> >> need to format certain cells to show a value from a specific date
>> >> forward.
>> >>
>> >> Example:
>> >>
>> >> Car payment needs to be applied on the 15th and remain there when the
>> >> month
>> >> rolls over. So on the fifteenth of May, I need cell C5 to display
>> >> $350.00,
>> >> but I don't want May's C5 to reset back to 0 when the date rollovers
>> >> over to
>> >> June 1st.
>> >>
>> >> I hope I am clear on this.
>> >>
>> >> Thanks for you time and attention,
>> >> Brian
>> >
>> >
>> > -- 
>> > via135
>> > ------------------------------------------------------------------------
>> > via135's Profile:
>> > http://www.excelforum.com/member.php?action=getinfo&userid=26725
>> > View this thread:
> http://www.excelforum.com/showthread.php?threadid=537605
>> >
>>
>>
>
> 


0
5/2/2006 12:30:15 PM
Reply:

Similar Artilces:

Display text with Unicode format (ex: Chinese) on controls written by VC6.0
Hi all, We can write a project with Debug-Unicode mode and display text with Unicode format? Ex: Chinese or Japanese Idea? Thanks. >We can write a project with Debug-Unicode mode and display text with Unicode >format? Ex: Chinese or Japanese With VC6 you can build a debug (or release) Unicode project that when run on a proper Unicode platform (like XP) should be able to display Chinese or Japanese texts (providing the font you're using has the characters). Dave Tittle bar, menu, button can display text with Unicode format when i build a debug Unicode project? We can use fo...

how do i enter a date quickly without using slashes or dashes?
i want to enter a date by only using number keys but i want the outcome to have the slashes in it i.e. 060204 --> 06/02/04. just like if you enter a currency like 602.04 it comes out like $602.04 and you don't have to add the $ because of how the cell is formatted. Date And Time Entry http://www.cpearson.com/excel/DateTimeEntry.htm -- --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "vodoris" <vodoris@discu...

.dat format
Can anyone advise why a .xls spreadsheet, when sent as an attachment in an Outlook email, arrives at one destination in a .dat format (and they cannot open this) while it arrives at another location when sent exactly the same way as a .xls spreadsheet? This sometimes happens when sending a Word .doc as an attachment as well. Lewis Shanks Lewis Try sending in "Plain Text" format to all receivers. Some email clients do not handle RTF. Gord Dibben Excel MVP On Mon, 27 Sep 2004 08:02:07 -0700, "Lewis Shanks" <lewis@alexanderscollection.com> wrote: >Can an...

How do I delete cell formats but not the content...
I am trying to copy data from Excel to use in Word and would love to get rid of the cells but not the data that is stored within the cells. What did you try? And what happened when you tried it? I usually select the range, edit|copy and Edit|paste into MSWord (using Office 2003 menus). On 05/17/2010 10:27, Penndaryl wrote: > I am trying to copy data from Excel to use in Word and would love to get rid > of the cells but not the data that is stored within the cells. ...

New Record, last Date + 1 different records
I have a datefield in a form that I would like to have autofilled when I enter a new record. Right now I have something like this: If Me.NewRecord then Me.DateGauged = DMax("DateGauged","tblGaugesInput") + 1 End if My problem is that it just looks for the max date of the whole table. I would like to narrow it down to a specific group of records in the table and do the last date + 1 on that group: Example of my table. TankNumber DateGauged Measurement 8555 2/1/2008 8" 8555 2/2/2008 9" 8556 2/1/2008 ...

Data from .txt file being read in as date, and should be text
I have a macro that reads in data from a .txt file. The data contains user IDs that consist of the first 4 letters of their name and 4 numbers. When names are read in that begin with MARCxxxx (ie MARC1234), excel is formating them as a date, and I lose the original data (even when I try to format them after the import, they are a number and the letters are not there. I've tried to format the column that they data read into before the import, but excel still imports them as a date. How can I make the import be read as text? Here is the code for the section I am reading in data for. Do Whi...

Outlook 2003 can't default to HTML format
I've got a user who wants to use HTML fomat for outgoing emails as default but I set it to HTML in tools...options.... but every time we try to create an email it defaults back to plain text. No other users have this problem. I've tried creating a new outlook profile and did clean rules in run prompt. Any ideas?? Outlook 2003/XP prof on MS exchange server. Is this when creating a new mail message or when replying/forwarding? Is the message format directly Plain Text after choosing "New" or only when it arrives? Are you using Word or Outlook as the email ed...

Conditional Format -- shading ten fields in a row reacting to colu
I have looked through a number of the c.f. posts, and have seen one similar to what I need from December of 2004 [I'll post below], but it's not quite what I'm looking for]. I have a status column [E] that has approximately five different phrases that can go in them. One of them is: Completed And when that happens, I'd like the row it is in [but just the ones that I'm working with, which are A through F, to be shaded. Now, from the post below I have seen how it can be done for one particular row at a time, but I really need to be able to do the whole thing at once,...

How do I restore format cells menu?
I am working in Excel 2002, multi-page workbook. On some pages "format cell" is not operational - neither from the toolbar or by right-clicking mouse. Command is there but produces no result. On other pages in the same book all is well. There are no protected pages, and this is happening only in one particular workbook. Have tried reset to no avail. When copy and paste material to properly functioning page problem is carried as well. Any ideas would be muchly appreciated. ...

Date formatting #7
Hope someone can help me with this. I have two colums's of date and time i.e. Cell H12 has "05/02/2008 11:03" and Cell N12 has "07/03/2008 11:35" H13:H60 and N13:N60 has more times I need to find the difference between these two times in hours and have it in Cell O12 The problem i'm having is as follows, I need it to be hours in working days, i.e Monday - Friday 8AM - 6PM. Is this possible? Looking forward to any help possible, thanks, Niall. =(NETWORKDAYS(H12,N12)-(WEEKDAY(H12,2)<=5)-(WEEKDAY(N12,2)<=5))*10+ ((("18:00"-MIN("18:00&...

conditional formatting dependent on cells not next to eachother...
I want to apply conditional formatting, but I can't figure out th formula. I have a row that contains 3 criteria I need in order to color the row but the 3 criteria are not the same type of cell. (1 cell is a Yes o No, and the other 2 are dates.) J3 must be a Y or N K is text L is text M is a date N is a date O is a date P is a date Q is a date R is a date Here is my criteria: J3 must be Y, N3 or P3 must have an entry, and R3 must have an entry. If all of these criteria meet, then turn the row the color. Thank you for any and all help you could provide. Angel -------------------...

Personal format of an excel spreadsheet as a template?
I need to use the format of an Excel spreadsheet that I have created as a template for other worksheets in a book and other books. How can I accomplish this, if possible? Create a new workbook with one sheet and format as you wish. File>Save As>File Type>Template(*.xlt) Name it SHEET(Excel will add the .xlt extension. Store this SHEET.XLT in your XLSTART folder. It will be the default worksheet for Insert>Worksheet. Gord Dibben Excel MVP On Wed, 19 Jan 2005 17:29:01 -0800, Nukinhawg <Nukinhawg@discussions.microsoft.com> wrote: >I need to use the format of an E...

Any way to perserve Pivot Table formatting
At least the borders? We have a pivot table that has many columns of data (18 months) and it is very useful to have an underline or row shading to help follow across this large table. We can set the borders and it works OK, but as soon as the data is updated, the format is removed. Even as simple as selecting one of the categories from the drop down clears the formatting. This doesn't seem right. What are we doing wrong? Should the formatting be set immediately after creating the table? Or is there some trick to this? Tried to pick a report style, but picking one seemed change the table...

Copy cell appearance but not conditional formatting
I have a spreadsheet with some conditional formatting. How can I cop the appearance of the cells without the formatting condition -- Message posted from http://www.ExcelForum.com Hi AFAIK this is not possible >-----Original Message----- >I have a spreadsheet with some conditional formatting. How can I copy >the appearance of the cells without the formatting condition? > > >--- >Message posted from http://www.ExcelForum.com/ > >. > Hi! I suggest you copy the formatting and then remove the conditiona formatting. This latter should be fairly quick, using t...

calculate 6 months forward from a date
Please can someone help, I need a formula to calculate a date 6 months forward from another date and return the future date as the answer in a cell. ie: start date 27/01/2010 answer will be 27/07/2010. Any help would be much appreciated. Thanks in advance Viv =DATE(YEAR(A1), MONTH(A1)+6, DAY(A1)) Where you date is in cell A1 -- HTH... Jim Thomlinson "Eclaires" wrote: > Please can someone help, I need a formula to calculate a date 6 months > forward from another date and return the future date as the answer in a cell. > ie: start date 27/...

Nested IF condition
Hi, I have a NESTED IF formula with five conditions. This is producing an answer by exclusively satisfying one of the conditions. Is it by any chance possible to know which of the five conditions is being satisfied ? (say by returning a number from 1 to 5 represeting the respective condition) Alternatively is it possible to assign a colour to the answer produced depending on which condition was satisfied ? Your help in this respect is greatly appreciated Thanks in advance. Tony Hi It might help if you post your formula and explain why it matters which option is chosen. There may b...

Formatting User Defined Fields
I have an Outlook custom form with several user defined fields. Can I change the margins around the inside of the field so that I have a tighter fit for multiline data. Also in a numeric user defined field can I make the field blank or does it always have to be zero for the inital defnation on the form. Thanks. A numeric field would start at 0 or -1 or whatever, it can't be just blank that I know of. Unless you somehow subclass the controls and manually draw them yourself I don't see any way to change internal properties such as internal borders unless the properties are expose...

date format not working
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) all of a sudden an excel file won't format a cell as Date. Even though the rest of the file does?! <br><br>I've tried formatting the cells in question and even though it shows dd/mm/yy it still returns a random number 3029989 or something <br><br>is there someway of resetting excel. removing some preferences file or something? thanks in advance It is possible for an Excel file to "corrupt". It's rare, but when it happens, you get strange indications such as this. The first thing I w...

Date and Time Problem
I need to have the current day autofill a cell but it needs to be base on a time change at 0700 hrs. What I need is to be able to fill out m form, have the current date display in the cell but when the syste clock changes to 0700 the date will roll up to the next day. 0100 hrs 4/18/06 0600 hrs 4/18/06 0900 hrs 4/19/06 2059 hrs 4/19/06 0100 hrs 4/19/06 0500 hrs 1/19/06 0705 hrs 1/20/0 -- JDBARNES6 ----------------------------------------------------------------------- JDBARNES63's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3363 View this thread: http://www.e...

conditional count 05-14-10
Any simple way to count how many 1s and how many 2s here? 2 1 2 2 2 1 2 1 1 2 1 1 On Fri, 14 May 2010 12:03:01 -0700, TimW <TimW@discussions.microsoft.com> wrote: >Any simple way to count how many 1s and how many 2s here? >2 >1 >2 >2 >2 >1 >2 >1 >1 >2 >1 >1 Have a look at the worksheet function COUNTIF Hope this helps / Lars-�ke Something like this perhaps. This will give you a count of how many 1's there are. Otto =CountIf(A1:A20,1) "TimW" <TimW@discussions.microsoft.com> wrote in ...

Detecting missing dates....
> 5/7/04 16:03 Roger > 5/10/04 9:02 Roger > 5/10/04 15:59 Roger > 5/11/04 8:57 Roger > 5/11/04 15:59 Roger > 5/12/04 9:06 Roger > 5/12/04 15:59 Roger > 5/14/04 8:19 Roger > 5/14/04 16:30 Roger > 5/16/04 9:27 Roger > 5/16/04 16:12 Roger Above is part of a list of time and dates at which Roger went to a office. Is there a way to detect / find out on which day, Roger didn' go to the office? As u can see, 5/13/04 and 5/15/04 are missing, becaus he didn't use his attendance card.....So how do I find out on whic dates he was absent using Excel -- Message ...

conditional formatinf of graph series
I have a spreadsheet where the font is colored based on conditional formatting. Is there a way to have the graphed series of these values match the format color. Thank-you Zb Hi see here with Jon Peltier: http://peltiertech.com/Excel/Charts/ConditionalChart1.html -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "Zb Kornecki" wrote: > I have a spreadsheet where the font is colored based on conditional > formatting. Is there a way to have the graphed series of these values match > the format color. > Thank-you > Zb Hi Zb, We've been aski...

Get external data-formats too
I have a file that gets data from another Excel spreadsheet. I want to have it retain the formatting (ie. indents) that were made on the imported file. Each time I refresh now, it left justifies everything...any ideas Thanks Hi, If your sheet just gets its data with '=Sheet1!A12'-style formulas, I don't think there's any way short of a macro reformatting the data like the old. Obviously, you can manually copy and paste special to get most of what you want, but it didn't sound like that's your situation... jeff >-----Original Message----- >I have a file that ...

Filtering junk email with HTML format
Is there a way to filter out junk email that has HTML format? Almost all spam is coming in HTML format now and I can't find a setting in Outlook 2002 to filter out these types of junk mail. Thanks! Tim Hailey >-----Original Message----- >Is there a way to filter out junk email that has HTML >format? Almost all spam is coming in HTML format now and I >can't find a setting in Outlook 2002 to filter out these >types of junk mail. Thanks! > > >Tim Hailey >. >Tim, You would have to have a third party package, I use Trend eManager which works with Tr...

Conditional Format Help
How do I write a conditional format that applies to "=$N$13:$N$22,$N$31:$N$40" where If $L13<$N13 fill the cell RED. This is what I have in the Format values where this formula is true box: "="$L13<$N13". But it doesn't work. Any ideas? Thanks Try getting rid of the quotation marks "Ayo" wrote: > How do I write a conditional format that applies to > "=$N$13:$N$22,$N$31:$N$40" where If $L13<$N13 fill the cell RED. This is > what I have in the Format values where this formula is true box: > "=&quo...