Excel worksheet programming for time restrictions.

I have several cells (properties) that will be visible or 
not depending on the time of day. I am using VBA to set 
the value of the properties and need a way to use not just 
the TIME function to establish the system time but, a way 
to test for a period of time, for example the DAY SHIFT, 
SWING SHIFT AND GRAVEYARD SHIFT. Using If Then Else 
conditional statements to test the system time to a span 
of time to indicate the shifts aforesaid.
What I am not clear on is the syntax in VBA to use when 
identifying the shifts and testing for conditions met.
HElP?
0
bhaynes (1)
1/6/2004 9:54:48 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
749 Views

Similar Articles

[PageSpeed] 28

Is this any help?

Dim tim0800
Dim tim1500
Dim tim2200
Dim tim2400
Dim tim0000
Dim tim0500

    tim0800 = TimeSerial(8, 0, 0)
    tim1500 = TimeSerial(15, 0, 0)
    tim2200 = TimeSerial(22, 0, 0)
    tim2400 = TimeSerial(24, 0, 0)
    tim0000 = TimeSerial(0, 0, 0)
    tim0500 = TimeSerial(5, 0, 0)

    If (Range("A1").Value >= tim0800 And Range("A1") < tim1500) Then
        MsgBox "Day Shift"
    ElseIf (Range("A1").Value >= tim1500 And Range("A1") < tim2200) Then
        MsgBox "Swing Shift"
    ElseIf (Range("A1").Value >= tim2200 And Range("A1") <= tim2400) Then
        MsgBox "Graveyard Shift"
    ElseIf (Range("A1").Value >= tim0000 And Range("A1") < tim0500) Then
        MsgBox "Graveyard Shift"
    End If


-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"bhaynes" <bhaynes@jacksoncasino.com> wrote in message
news:04f401c3d49f$b444c440$a101280a@phx.gbl...
> I have several cells (properties) that will be visible or
> not depending on the time of day. I am using VBA to set
> the value of the properties and need a way to use not just
> the TIME function to establish the system time but, a way
> to test for a period of time, for example the DAY SHIFT,
> SWING SHIFT AND GRAVEYARD SHIFT. Using If Then Else
> conditional statements to test the system time to a span
> of time to indicate the shifts aforesaid.
> What I am not clear on is the syntax in VBA to use when
> identifying the shifts and testing for conditions met.
> HElP?


0
bob.phillips1 (6510)
1/6/2004 10:17:05 PM
Reply:

Similar Artilces:

hide title bar of a dialog box on run time
i have a dialog box, and 2 button on a dialog. i want to add functionality that when i will click button 1 the title bar will disappear, and when i will click second button it will appear(become visible). thanks >i have a dialog box, and 2 button on a dialog. i want to add >functionality that when i will click button 1 the title bar will >disappear, and when i will click second button it will appear(become >visible). The following snippet toggles the caption bar: DWORD CurrentStyles = GetWindowLong( m_hWnd, GWL_STYLE ); SetWindowLong( m_hWnd, GWL_STYLE, CurrentStyles ^ WS...

Excel formatting toolbar icons
Simple suggestion: Increase decimal icon should have a green arrow and decrease decimal a red arrow. Instead they are both blue and I always click the wrong one first or have to think about which one to push. Don't make me think - it hurts my head! From the MS web site: ========================= World Wide Web To send a comment or suggestion via the Web, use one of the following methods: . In Microsoft Internet Explorer 6, click Send Feedback on the Help menu and then click the link in the Product Suggestion section of the page that appears. . In Microsoft Windows XP, click Help a...

Creating bell curve in excel
Hey, I'm trying to create a chart utilising the bell curve style, when I have entered all the data and fomulae it seems to come back with an incorrect value/result on the x-axis. ie: the highest value is 503 but it is showing up as high as 800. I am using a guide I discovered which is located here - http://support.microsoft.com/kb/q213930/ Does someone know of or could help make a step by step guide on how to make a functioning bell curve chart in excel 2003. Any help appreciated Regards Luke This should help you http://www.tushar-mehta.com/excel/charts/normal_distribution/index....

need help with formula for a newbie excel guy
this might be a stupid question to all of you excel experts out ther but please help me. cell c14 = 0 cell d14 = 5 in order to get an N/A in the equation cell, here is the formula that put it. =IF(C14=0,"N/A",D14/C14-1) is it possible to get a formula to have N/A if either cell is "0"??? please help, thank you -- ben80 ----------------------------------------------------------------------- ben803's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3513 View this thread: http://www.excelforum.com/showthread.php?threadid=54896 Instead of ...

Excel 2007 crashes upon changing the properties of a data connection
Hi there, I'm using a Dutch Excel 2007 (SP1) with a spreadsheet that has a few tabs with text file (CSV) based data queries. When I go to the properties of one of the data connections, uncheck the "Prompt for filename upon refresh" and uncheck the "Auto refresh upon spreadsheet load" Excel crashes 10 out of 10 times! Has anyone seen this before? ...

Protecting Workbooks in Excel 2003
I cannot seem to protect a workbook in Excel 2003. I can protect Worksheets, but not workbooks. Do I have to protect all the worksheets in a workbook before protecting the workbook? Seems superfluous to me!! I don't know what I am doing wrong. I click on Tool, Protection, Protect workbook, but have not assigned a passwor. Even though I save the workbook, it can still be modified. Help please!!! Thanks by default, when you protect a workbook, the "Structure" of the workbook is protected. So, to easily test whether a workbook is already protected, try inserting new workshe...

time stamp #2
I have done many time stamp formulas before, but am needing something a little different.... In column J, I need a formula that would give me date stamp whenver the adjacent cell in column I has the word "closed" Or would this work better as a vb code for the sheet? Thanx. Certainly would Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "H1:H10" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If ...

Closing down Excel work books #2
I have made an excel spreadsheet that takes its information from a access data base the information automatically updates each time exce opens, I do not want to save the information. I would like it to clos down wthout the popup -- peterspindrif ----------------------------------------------------------------------- peterspindrift's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1474 View this thread: http://www.excelforum.com/showthread.php?threadid=26362 ...

Multiple Start and End Times
Hello Everyone, I would like to run one query that can accommodate entering twelve possible date ranges and output the data all in one query. I could accomplish that by writing 12 queries and attaching each one to a start and end box on a form and then doing a Union Query to obtain one result. I think this might be the long way to accomplish this. Is there a better way to get the result on one query for multiple Start and end Date Ranges? Thanks -- Message posted via http://www.accessmonster.com ...

Neat tool for mapping (geocoding) addresses straight from Excel
I developed a free tool that lets you geocode addresses from excel real easy by just copy/pasting them into this web page: http://www.batchgeocode.com You can see the results on a map, or copy/paste the lat/long coordinates back into Excel. You can geocode up to 500 addresses at a time. Pretty cool I think, let me know if anyone has suggestions for making it easier. Thanks, -Phillip ...

How do I arrange numbers in asending order in Excel?
I have a list of numbers and I need them in order from least to greatest. How do I set the column of numbers to automatically be in order? Select the range containing your data. Then click on DATA ... SORT ... ASCENDING. "Linds" <Linds@discussions.microsoft.com> wrote in message news:1B307702-C88F-4359-9464-00133298E5A8@microsoft.com... >I have a list of numbers and I need them in order from least to greatest. > How do I set the column of numbers to automatically be in order? Select the column(s) then pulldown Data > Sort... and sort ascending. Of course t...

Run Time Error on trying to save workbook
This code worked perfect when it was in the User Form Code Window, but I moved it to a module and I get a Run Time Error "424' Object Required Code in the User Form Window -------------------------------------------------- '******************************************************* 'Save Installer Forms 11 Control Button 'Located in M3_Save_Workbook '******************************************************* Private Sub Save_Installer_Forms_11_Click() Call Save_Installer_Forms End Sub Code Located in the module: Declarations -----------...

HELP with excel table/chart
I have a question that I am almost sure has a very simple answer. have a small table with 3 columns. The first column is density and ha a set of values beneath the column heading. The second column is heigh and has a set of values beneath the column heading as well. However the is a second set of values in the third column that go with th heading for the second column and need to correspond with the value from the first column. I want to combine the values from the 2nd an 3rd columns so that I can make a scatter plot with one set of point for height. here is an example blah blah 2 1...

saving excel as webpage so that hyperlinks load into different window
how can I save my spreadsheet as an html file *so that hyperlinks ope in a new window?* I've already tried tweaking the html file myself, but I was wonderin if there was more efficient solution. Stuff tried: -tweaking html (setting base target in html does n/work with the codin that excel puts out, maybe syntax? setting target="_blank" for ea. lin works but is too laborious) -working setting base target in exce -- rsa ----------------------------------------------------------------------- rsai's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1542 ...

Opening Excel #6
Hi All can anyone help me. When i am opening an excel spreadsheet using the open office document icon in the start menu. excel opens but and gets to the ready stage but the file does open at all. Can anyone shed any light on this one for me Thanks Stuart Hi Stuart! Files Not Opening from Explorer or from Email Double clicking on files from within Explorer or attached to e-mails should cause Excel to start and to load the file. Often this fails. Excel starts but the file is not loaded. However, with Excel open the file may load with a normal double click and / or it can be opened from ...

Chart of Times
I am working with a simple data set comprised of two columns and 24 rows. In column ("A"), I have the numbers 1-24. In column B, I have these random times: 4:57 5:16 2:17 1:20 1:11 1:21 1:19 1:11 1:37 2:56 3:29 4:09 5:59 5:23 5:22 3:59 4:04 1:51 2:06 2:16 2:08 1:28 1:24 1:24 The chart I would like to make would have the numbers 1-24 on the X- axis and the times listed above on the Y-axis. Ideally, this would be a column chart. My Issue: The times listed in column ("B") are formatted as time. When the chart is created the X-axis an...

2008 Excel Print Preview
I do not see print preview under file or anywhere else...wondering if this is specific to my install or it is hidden In article <ee8d6ae.-1@webcrossing.caR9absDaxw>, done@officeformac.com wrote: > I do not see print preview under file or anywhere else...wondering if this is > specific to my install or it is hidden Neither. Since all actual print operations are done via the OS, the separate Print Preview function has been removed from XL08. You can generally use Print Layout view to get an good idea of what the printed page will look. Otherwise, choosing File/Print and clic...

Shared Excel Worksheet missing.
I have three users running XL 2000 with the latest SR. They claimed after trying to save this large shared Excel spreadsheet to the server, it said it did not exist. The only option was for this user to save to his local desktop. After trying to access the document on the server, it no longer existed. I'm wondering if the server some how locked the file after another user had it open and closed the spreadsheet, only to create a hidden locked file on the server preventing the user to save the spreadsheet to the same location? --tj Did they possibly get this error message? ...

2005 Excel User Conference #2
Just a reminder about the 2005 Excel User Conference. Details can be found at: http://www.exceluserconference.com/ The conference will take place on Friday, September 16, 2005 - Saturday, September 17, 2005 and will be held at Stockyards Hotel Fort Worth, Texas USA. Mark your calendars and make plans to attend. This will be your opportunity to meet some of the regular posters and learn from some of the "masters". Don't miss out!! Everyone is welcome to help spread the word about this event. Messages posted in other forums in which you participate would be appreciated. We would...

Worksheet functions
How do I create a function that will add an amount according to the date? Like a late fee? Have a look in HELP index for SUMIF -- Don Guillett SalesAid Software donaldb@281.com "Nadji" <Nadji@discussions.microsoft.com> wrote in message news:90C4A1BC-568E-4A6B-9075-A98E96738526@microsoft.com... > How do I create a function that will add an amount according to the date? > Like a late fee? If you are asking for a way to calculate a late fee, here's one way: A1: $100 (Invoice Amt) B1: 05/25/2005 (Due Date) C1: =IF(TODAY()-B1>10,A1*5%,0) (Late fee: in ...

Excel 2003 #2
Our company has just upgraded us to XP, along with that we now are usin Office 2003. I like some of the new Excel error tracking, but am havin recurring problems with a couple of other aspects. The first is - having set the toolbars with all the things I like t have to hand - when opening some sheets I suddenly get WEB an REVIEWING toolbars popping up - not only do these reduce the amount o screen when closing said sheets they remain - WHY DO THEY APPEAR? If right click and uncheck the box they go, but not permanently. The other annoyance is I use CTRL+INSERT to copy then ENTER to past af...

How do I highlight a word or sentence within Excel?
Is there a way to highlight certain words or sentences within Excel. I don't want to hightlight the entire cell 9I know how to do that, I just want to hightlight certain text. Thank you. You can select the words (or characters) in the formula bar, then use Format|cells to change some formatting. This won't work if the cell contains a formula or contains real numbers. It has to be text. cands wrote: > > Is there a way to highlight certain words or sentences within Excel. I don't > want to hightlight the entire cell 9I know how to do that, I just want to > hightli...

How to check whether MS Excel is installed or not in the System
Hi All, I hav an MFC apllication, which is used to store some report data to an Excel file (using Excel automation process). Before i store the data to excel file, i need to check whether MS Excel is installed in the system or not. if excel is not installed then i have to store the data to text file. So how do i check whether excel is installed or not using MFC programming? -- Thanks Ash Jings >I hav an MFC apllication, which is used to store some report data to an >Excel file (using Excel automation process). > >Before i store the data to excel file, i need to check >w...

Excel on desktop
When I open an excel doc that has been saved on the desktop an error comes up that the path can not be found. This is not a short cut. If I open a new excel and go to file and drill to it on the desktop it opens just fine. I've tried save as, copying the data to a new sheet and it all comes up the same. I did a repair on office then a clean install to no avail. Any thoughts? Is this an Office problem or possibly a windows issue? Uncheck ignore other applications under tools>options>general - Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the ...

Excel error
This must be such a common error, that I'm surprised it's not explicitly "detected" by Excel on startup. Error symptom: Excel 2003 cannot open any vintage of .XLS file, and even on invoking Excel alone, the same error message occurs - "This file is not in a recognizable format. * If you know the file is from another program, ...." etc [OK] [Cancel] [Help] That's rather a cryptic error, and although it is very wordy and helpful, if you know what's actually wrong, it could be much more informative. Situation: I had Office 2002 installed, and Ad...