Conditional Multiple Time Ranges

I have pivot tables that pull from a Data Warehouse that regenerates during 6 
time ranges each day.  I want to put a message on each pivot table that 
displays only during these time ranges.  I have succeeded in putting a 
running clock time on the sheet, but can't figure out how to display my text 
for these 6 ranges.

For example, between 8:00 AM and 8:15 AM and 6 other ranges thereafter I 
want to display text in a cell on the sheet that provides a message to the 
user.  How do I specify these ranges and how do I conditionally show the text 
for 6 ranges?

Thanks for your help.
0
Utf
11/18/2009 11:44:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
737 Views

Similar Articles

[PageSpeed] 54

Hi,

Type the text in a cell (say A5) and colour the font to white (the text 
should not be visible). Then apply the following conditional formatting rule 
to the cell - Formula is

=and(mod(now(),1)<=time(8,15,0),mod(now(),1)>=time(8,0,0)).  Apply the font 
to colour black

Now when you refresh the pivot, the conditional formatting should apply

-- 
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"KT" <KT@discussions.microsoft.com> wrote in message 
news:EF5BFE5C-4296-4E1A-9A93-53B5123E2AC7@microsoft.com...
> I have pivot tables that pull from a Data Warehouse that regenerates 
> during 6
> time ranges each day.  I want to put a message on each pivot table that
> displays only during these time ranges.  I have succeeded in putting a
> running clock time on the sheet, but can't figure out how to display my 
> text
> for these 6 ranges.
>
> For example, between 8:00 AM and 8:15 AM and 6 other ranges thereafter I
> want to display text in a cell on the sheet that provides a message to the
> user.  How do I specify these ranges and how do I conditionally show the 
> text
> for 6 ranges?
>
> Thanks for your help. 

0
Ashish
11/19/2009 12:01:30 AM
Perfect solution.  Thanks.



"Ashish Mathur" wrote:

> Hi,
> 
> Type the text in a cell (say A5) and colour the font to white (the text 
> should not be visible). Then apply the following conditional formatting rule 
> to the cell - Formula is
> 
> =and(mod(now(),1)<=time(8,15,0),mod(now(),1)>=time(8,0,0)).  Apply the font 
> to colour black
> 
> Now when you refresh the pivot, the conditional formatting should apply
> 
> -- 
> Regards,
> 
> Ashish Mathur
> Microsoft Excel MVP
> www.ashishmathur.com
> 
> "KT" <KT@discussions.microsoft.com> wrote in message 
> news:EF5BFE5C-4296-4E1A-9A93-53B5123E2AC7@microsoft.com...
> > I have pivot tables that pull from a Data Warehouse that regenerates 
> > during 6
> > time ranges each day.  I want to put a message on each pivot table that
> > displays only during these time ranges.  I have succeeded in putting a
> > running clock time on the sheet, but can't figure out how to display my 
> > text
> > for these 6 ranges.
> >
> > For example, between 8:00 AM and 8:15 AM and 6 other ranges thereafter I
> > want to display text in a cell on the sheet that provides a message to the
> > user.  How do I specify these ranges and how do I conditionally show the 
> > text
> > for 6 ranges?
> >
> > Thanks for your help. 
> 
0
Utf
11/21/2009 5:30:01 PM
You are welcome

-- 
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"KT" <KT@discussions.microsoft.com> wrote in message 
news:70E7F1DD-0D35-4246-A72E-2A4BF452E40B@microsoft.com...
> Perfect solution.  Thanks.
>
>
>
> "Ashish Mathur" wrote:
>
>> Hi,
>>
>> Type the text in a cell (say A5) and colour the font to white (the text
>> should not be visible). Then apply the following conditional formatting 
>> rule
>> to the cell - Formula is
>>
>> =and(mod(now(),1)<=time(8,15,0),mod(now(),1)>=time(8,0,0)).  Apply the 
>> font
>> to colour black
>>
>> Now when you refresh the pivot, the conditional formatting should apply
>>
>> -- 
>> Regards,
>>
>> Ashish Mathur
>> Microsoft Excel MVP
>> www.ashishmathur.com
>>
>> "KT" <KT@discussions.microsoft.com> wrote in message
>> news:EF5BFE5C-4296-4E1A-9A93-53B5123E2AC7@microsoft.com...
>> > I have pivot tables that pull from a Data Warehouse that regenerates
>> > during 6
>> > time ranges each day.  I want to put a message on each pivot table that
>> > displays only during these time ranges.  I have succeeded in putting a
>> > running clock time on the sheet, but can't figure out how to display my
>> > text
>> > for these 6 ranges.
>> >
>> > For example, between 8:00 AM and 8:15 AM and 6 other ranges thereafter 
>> > I
>> > want to display text in a cell on the sheet that provides a message to 
>> > the
>> > user.  How do I specify these ranges and how do I conditionally show 
>> > the
>> > text
>> > for 6 ranges?
>> >
>> > Thanks for your help.
>> 
0
Ashish
11/22/2009 4:25:21 AM
Reply:

Similar Artilces:

Auto Calculate Monthly Time Pace
I need to show how far into a month we are at any given time in % form. I want this to change every day automatically. Example: Today is day 27 of 31 days in January - 87% if today was 2/17/10 it would need to show 17/28 or 61%, 12/20/10 would be 20/31 or 65%, and so on for each day throughout 2010 (12/20/10 would be 65%) Is there a formula or function to do this? Thanks! Heather "HeatherJ" wrote: > I need to show how far into a month we are at any given time in % form. [....] > Today is day 27 of 31 days in January - 87% One way: =day(today()) / ...

Forward multiple emails
I am trying to pull together several emails from multiple sources and forward them out with a new email cover letter. I am trying to avoid forwarding each email separately. I want the emails to arrive together with the new email giving a summary of each. Select all the messages and press Forward. -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -What do the Outlook Icons Mean? -Create an Office 2003 CD slipstreamed with Service Pack 1 ----- "Hendrix" <anonymous@discussions.microsoft.com> wrote in message news:3bbf01c48f9d$4aabd870$a301280a...

Isinteg
I ran the following on a private store that had the following size (.edb - 39,816,264 & .stm 35,145,736). On the first pass it took approx. 18 hours to complete, now on the second pass I am well over 24 hours and it is just a little over half a way done. This is exchange 2003 running on windows 2003. Is this normal? I ran the isinteg with -fix -test alltests. -- Eric Sabo NT Administrator Hi Eric, At Microsoft we typically use 3-4 gig per hour as a rule of thumb although alot depends on hardward, disk configuration, etc. Craig -- Craig Philbeck a-cphil@online.microsoft.com Mi...

Today Conditional Format
I have a column which calculated a date based on 3 working days from a date input into a diff column. What I want to do I have the calculated date appear bold if that date has passed based on the current date. For example if I input 01/08/11 the next column calculates 04/08/11 (3 working days). I want the caluclated date to appear bold when I open the spreadsheet on the 05/08/11 ro show the calculated date has passed. I can use conditional formatting but can't get the syntax right Regards Andy Win XP Pro Office 2010 Andy Roberts presented the following explanation : > I ha...

Multiple detail areas in one report
We are creating a database which uses several forms to input data into a Master_Data table. There are several categories for the data ex: Full Service Driver, Trade Driver, Misc Receipts, Misc Payouts. I need a report that breaks these categories out in detail like so: Cashier Driver# Driver Name Full Service Checks Full Service Currency Full Service Coin Subtotal Cashier Driver# Driver Name Trade Checks Trade Currency Trade Coin ...

Time Zone Change
When I turned on my pc this morning I realized that my "adjust for daylight savings time" option was not checked off in outlook calendar options. However, when I check that box, it changes all my appointments by 1 hour. For example, I have an appointment for 11am and it moves it to 12pm. What can I do about this? Your help is very much appreciated. ...

CONVERT TIME & TRIM DATE
I need help converting time to an AM/PM time format our database displays time in 4 digits : 0006, 0737, 1217, 2149. I am finding that 0006 is 12:06 AM and 1217 is 12:17 PM, etc. The date displays: 2007-03-09 00:00:00.000, how do I have it display the date as 03-09-2007? Thanks. Specify the display format of the control or field: mm-dd-yyyy RENEE705 wrote: >I need help converting time to an AM/PM time format >our database displays time in 4 digits : 0006, 0737, 1217, 2149. I am >finding that 0006 is 12:06 AM and 1217 is 12:17 PM, etc. >The date displays: 2007-03-09 00:00:00.0...

append multiple columns
I have a time consuming task and was trying to find a way to append multiple columns of data (with blank cells) under column "A". Currently, my columns have data out to column "M", but this may increase or decrease. From: A B C etc. Thru Column "M" 1 Data1 2 Data 2 3 Data 3 Data X 4 Data 4 Data Z 5 Data 5 6 Data 6 Data Y Into: A B C etc. Thru Column "M" 1 Data1 2 Data 2 3 Data 3 4 D...

Multiple dates need to obide by
Here is the problem I know when I am going to start the task and the last day the task can be finished by. My question is I have three other dates that need to be added in between the start and finish date. These three dates check on the task and make sure everything is going alright. Should I just add in three more start dates as start date1, start date2, and start date3 or is there a better way to go about doing this. Also for each task, I have alot of information where I had to add more text columns and it is getting pretty messy to look at. Is there a way to be able to m...

Multiple Accounts, Same Server using SPA fails silently.
After upgrading from Office XP to Office 2003 it seems like the outlook client fails to pick up mail from our POP3 server (Windows 2003 Server POP service) for any but the first account that's configured in outlook. NO error is reported but the mail is only downloaded from one of the accounts. Any ideas? thanks! Jahyen Could you turn on diagnostic logging (see http://support.microsoft.com/default.aspx?scid=kb;en-us;Q300479) and post the OPMLog.log file (as an attachment) after you try to receive? -- Jeff Stephenson Outlook Development This posting is provided "AS IS" wit...

Excel 2003 - VBA
Hi: I need to clear 31 sheets of data (daily downloads) and cycling through them is timely and just not very elegent. (Look at me, trying to be elegent when I can barely use VBA!) What I did was turned on macro recording, selected several sheets then depressed "Delete". After responding "Yes" to three popups asking if I wanted to clear the QueryTables, it seemed to work. When I went back to look at the generated Macro, it got hung up (Application-definded or object-defined error) on the line deleteing the querytable, and this seemed to happen before I even re-executed the...

Two Condition Vlookup?
I am currently trying to create a function that searches through an array for two exact column values which allows me to retrieve a third column value. For example: Type of Fruit Date Packaged Amount of Fruit Apples June 100 Oranges June 50 Apples July 75 Grapes July 50 In this example, I would be looking to retrieve the Amount of Fruit (Apples) that was packaged in July. I would be putt...

Selecting Multiple Controls
I have about a hundred checkbox controls on a worksheet. I need to select differing subsets of multiple controls in order to move or modify them. I have entered 'Design Mode' on the 'Developer' tab and activated the 'Select Objects' option from the 'Find and Replace' feature on the 'Home' tab. With that combination I can drag over the controls, but this action does not select the controls. What must we do in order to drag and select large groups of controls? Max You can select multiple controls by using control-click to add addit...

Need help with conditional formula (Excel 97)
Can anyone give me the correct syntax for the following formula: if A1 is blank, and B1 is more than zero, then display B1, otherwise display nothing (blank cell) Thank you JD =IF(AND(A1="",B1>0),B1,"") Success! -- met vriendelijke groetjes "Jake D" <JakeD@djhtend.com> schreef in bericht news:crkto4lv30u7uujv27ek8okr0us5t9oj10@4ax.com... > Can anyone give me the correct syntax for the following formula: > > if A1 is blank, and B1 is more than zero, then display B1, otherwise > display nothing (blank cell) > > Thank you > > ...

Query condition by date range
I'm trying to create a query to base a report from. I need it to return records within a date range that needs to be specified, as in specifying the beginning date and having the current date as the end date. A field is included in the query that has listed dates in the format mm/dd/yyyy. How would I write the criteria? I have part of it ready - [Please enter starting date:] . I know that's how you get the little question window. On Tue, 4 Dec 2007 14:51:00 -0800, silva wrote: > I'm trying to create a query to base a report from. I need it to return > records within a...

View multiple user exchange attributes.
Is there ANYWHERE that I am able to view a report or something that will show me... The users name and if any radio buttons are selected in User\Properties\Exchange General\Delivery Restrictions\Message Restrictions If anyone has the answer, I would greatly appreciate it. I have tried a bit of LDAP but I get lost really quick after the cn\ I have searched around for Exchange reporting tools, but they are mostly concerned with throughput and sizes of mailboxes. Anyway, thanks for the help. ...

Convert text to time value
I have a series of time values in a 'General' format. They are of the type: 184525 Which is 18:45:25 or 6:45:25 pm. A time which is am would be of the type: 12345 Which is 1:23:45 am. Is there a way to convert those 'General' values to an Excel serial so that I can figure out the difference between two times? I've seen a bunch of examples on the net, but none of those that I have found deal with this format that I can tell. Thanks. Hi try =--TEXT(A1,"00:00:00") -- Regards Frank Kabel Frankfurt, Germany "Andy" <amelton@gmail.com> schrieb...

Is there a registry key to specify the idle time before tracking info is processed?
Hello, In Outlook, read receipts and delivery notifications are processed in idle time, which seems to be about 1-2 minutes. Anyone know if a registry key specifies the exact idle time? We're looking to reduce it, like to zero if feasible! Thanks, - Alan. How did you come to this conclusion? Receipts are processed immediately = when using Exchange. Perhaps you need to specify your environment. --=20 Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. After furious head scratching, Alan asked: | Hello, |=20 | In Outlook, read receipts and de...

Conditional formatting if condition of an if statement is true and a string value is displayed
Dear Experts: I got a nested 'IF'-Formula in an excel cell that returns nothing if the condition is false. I would like to conditionally format this cell with grey shading if the condition is true and a string value is displayed. How can this be achieved using excel conditional formating functionality ? Help is much appreciated. Thank you very much in advance. Regards, Andreas Let's say your doing this conditional formatting in cell K17. In conditional formatting (xl2003) choose the FormulaIs: option and enter: =$K$17<>"" choose your formatt...

excel 2003
I've got a problem with excel 2003. When I select some sheets to print them in 1 print job, it splits the printjob to 2 seperate printjobs. This normaly is not a problem, but since I want to print to pdf, pdf-printer will regerate 2 files. Does anyone know how to solve this problem? I searched the internet allready, but I only found 1 solution. This is to set quality all to the same option, eg. all to 600 dpi. This solution only work for excel 97, but I tryed it anyway. Did not work :-( Some ideas? Maybe you can provide more details. I created a workbook with 4 worksheets. I select...

Combo box selection updates multiple source fields in underlying t
I have one combo box with six columns. One value is tied to the underlying table. I want people to choose from the combobox, and have ALL 6 underlying fields updated according to the combo box selection. It updated the one field that has the underlying field as the record source, is there anyway to tie the remaining 5 columns to the underlying table? Currently, the combobox relies on a seperate table that populates its values (a lookup table more or less), but each field in the seperate table does have a corresponding field in my main table. I did the =column5(or whatever column...

Conditional Field in Queries
Is there a way to make a field in a query conditional so that it only displays the value if the value is equal to something specifically set? Thanks. Nick Nick.Korynski@gmail.com wrote: > Is there a way to make a field in a query conditional so that it only > displays the value if the value is equal to something specifically > set? Thanks. > > Nick Explain "the value is equal to something specifically set". -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com ...

How to return a 12 hour time format without the AM in an Access query
The format function acts differently in a query and after hours of testing, messing, and cursing, I give up. How do you return 12 hour format without the AM or PM from an Access query. In my table, I've defined the Start Time and End Time to use "Medium Time" format and it works if and ONLY if you simple include the field in the query. However, if you use the field in a more complex statement, it returns leading zeros and trailing seconds. Here's what I've tried.... 'This didn't work because it returns trailing seconds and AM/PM. '2:00:00 PM...

How do i use Conditional formatting for 3+ conditions ?
I have a text box in a report which highlights in colour current members in a list of past and present member names. I have six types of current members that I want to highlight, but with conditional formatting I can only use 3 conditions in the members [Type] field. I have tried [Type]="Full member" Or "Life member" but the "Or" doesn't work ! Is there a way to do this ? thanks .. Roger CF is limited to 3 contitions, but you can use OR in those expressions. If you set Condition1 to Expression, you can use: ([Type] = "Full member") Or (...

Help with Conditional formatting with Dates
Hi there, I have a spreadsheet in which I have to monitor various dates which are in different columns (i.e. (1) Date Tender Released, (2) Date Company Selected, (3) Date Draft Contract Forwarded, (4) Date Contract Signed etc...). Would if be possible to Conditional Format and have diffirent ROWs highlighted for each individual Contracts (I have 300 running contracts running) every time a date is filled in these different columns: Initial Step (1) = Yellow (Call for Tender released) Step (2) = Blue (Company Selected) Step (3) = Brown (Contract under Process) Step (4) = Green (Contract Si...