Date/Time Macro Puzzle

Hi, everyone--

Here is a macro that deposits the date and time into a cell, but which =
is=20
supposed to pop up a warning box when the time is after 5:00PM:

Sub NewDateAndTime()

Dim mPrompt As String
Dim mBoxStyle As Long
Dim mTitle As String
Dim mMsg As Variant

mPrompt =3D "It's after 5:00 PM! Click OK to enter time, but please =
remember=20
to enter TOTAL HOURS WORKED TONIGHT in the yellow box at right.=20
Thanks!"
mBoxStyle =3D 64
mTitle =3D "AFTER-HOURS ENTRY"

With ActiveCell

    .Value =3D Now
    .NumberFormat =3D "mm/dd/yy h:mm AM/PM"

    If Now Mod 1 > 17 / 24 Then
        mMsg =3D MsgBox(mPrompt, mBoxStyle, mTitle)
    End If

End With

End Sub

The puzzle is this: in its current formulation, a warning box NEVER =
appears,=20
regardless of the time of day. However, if I reverse the > sign in the=20
"If...Then" clause to <, a warning box ALWAYS appears, again regardless =
of=20
the time of day. (I have tried replacing the 17/24 designation with its=20
decimal equivalent [about 0.708333], but the same problem occurs.)

I have no idea what's going wrong. Any ideas how to get the warning to=20
appear only after 5:00 PM? I appreciate your help--this is driving me =
nuts!


0
3/15/2005 2:22:36 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
579 Views

Similar Articles

[PageSpeed] 53

There are probably a number of ways to skin this cat: one of them is to
use the Timer function, which returns the number of seconds elapsed
since midnight.  In your case 5pm is 17 hours after midnight, which
equates to 17*60*60 or 61200 seconds.  You could replace your code
If Now Mod 1 > 17 / 24 Then

.... with this:
If Timer >= 61200 Then

This method will save you a lot of conversion of time strings to
numeric, etc.

Dave O

0
CycleZen (674)
3/15/2005 2:34:54 PM
I'll give it a shot! Unfortunately I need to go to a meeting, so it's =
going to be=20
a while before I can test this out, but at least I've got something to =
try when=20
I come back.
>-----Original Message-----
>There are probably a number of ways to skin this cat: one of them is to
>use the Timer function, which returns the number of seconds elapsed
>since midnight.  In your case 5pm is 17 hours after midnight, which
>equates to 17*60*60 or 61200 seconds.  You could replace your code
>If Now Mod 1 > 17 / 24 Then
>
>.... with this:
>If Timer >=3D 61200 Then
>
>This method will save you a lot of conversion of time strings to
>numeric, etc.
>
>Dave O
>
>.
>
0
3/15/2005 2:48:49 PM
Reply:

Similar Artilces:

Macro on cell content
Dear all, this must be a common problem, and I thought I found the solution, but it doesn't work ... I'm probably missing something very simple. I want to execute a simple VB function in one cell, taking another cell as Parameter. Both, parameter cell and function result are strings. For example: A1: "this is content of cell A1" A2: =Convert(A1) and Convert is a VB function Function Convert(content As string) as String ' do something depending on content Convert = "result-string" End Function However, I always get a #NAME? Error in cell A2. The ide...

date stamp #2
I am a new user to excel and would like to know how I put a stamp in the corner of my workbook that says the file that it is under. Ex h:\xw\... Also would this change if I change the file it is in. Thankyou Jessica ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ One way: =LEFT(CELL("filename",A1), FIND("]", CELL("filename",A1))) Note that the file must be saved for this to work. In article <jdorion.vgeay@excelforum-nospam.co...

Date #6
hi I have a workbook that contains several formatted cells cell "A1" is formated as dd/mm/yyyy and has the today() formula in it what i want to do is to copy the value of "A1" and paste it into "A4" ie. "A1" = 13/01/2005 i only want the text value to be copied and pasted into A4 "A4" = 13/01/2005 When i try this i get the numeric value of 38365 which is excels text value of the date. I need the above format thanks in advance kevin Pre format or use edit>paste special or use a formula =text(a1,"yourformathere") or...

using macro to save a file produces runtime error 4198
I upgraded from Win-XP/Office 2003 to Win-7/Office 2007; the code below ran fine in Word 2003 but in Word 2007 generates runtime error 4198. The variables and directory location are valid. The parameters for FileName are updated for Word 2007; the grammer for the FileName appears correct (no grammer errors when macro runs as is, but grammer errors if I change the string.) When I substitute an actual file name in place of the variables, the code runs w/o error, but the Debug.Print statements generate correct strings. I feel kind of dumb- but what is different about use of variab...

Time Zone chagnes in 2003
I have read some articles about changing your time zone without effecting the times of appointments by exporting and reimporting, but all these KB's seem to apply to previous versions. Is this still the only way to accomplish this in 2003? And if so what will I loose if I do this (any effect on reoccurrence, linked contacts, categories, labels, locations, show time as, and all day events)? Or does anyone know of any way to do this easier (with a 3rd part app perhaps)? Thank you! it works for all versions. AFAIK, you won't loose anything, but you can try it with a few to see ...

Custom dat & time code
Does anyone know if it's possible write a custom cell-format code that=20 would look like the one Excel provides (mm/dd hh:mm AM/PM) but sets the=20 time in red? I've tried "mm/dd [RED]hh:mm AM/PM," but whenever I save = it,=20 Excel moves the red bracket to the beginning of the code so the whole = thing=20 is red. Help! Any ideas? I don't think you can. You can't format formulas like this (character by character). And you can't format numbers this way, either. (Anyway, I couldn't do it.) Wuddus wrote: > > Does anyone know if it's possible ...

Trying begin VBA template project, get error "macros disabled"?
I am beginning a project to code and update a Word template using VBA. When I try to run the form given to me I get error "The macros in this project are disabled?" Any suggestions? Thank you. Save the template in your templates folder, which is a trusted location. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "The Applicant" <The Applicant@discussions.microsoft.com> wrote in message news:D2E642C...

Time formatting issues
I am nearly finished working on my latest project, a data entry GUI in VBA (2003), and have run into difficulties dealing with time values. Brief project background: It is a simple data entry form which takes various bits of flight data and enters them onto a spreadsheet. The key entries I'm having trouble with are 'Time' values. Two fields are automatically populated with the appropriate 'Departure' and 'Arrival' times based on a 'ListIndex' run during initialization, which allows values for the 'Flight Numbers'. An 'If...Then' stat...

Add a date to a Userform
I am using Office 2010 in advance of roll-out in our organization. I have been testing some of our VBA enabled templates for compatibility and have run into a problem. Specifically a lose of the Calendar control. Since we have a number of Userforms that use the calendar control, I need to come up with some sort of replacement. Any suggestions? I thought there was a date picker control, but I can not find it (I have installed the complete Office 2010 Professional Plus) The calendar control uses the library c:\program Files\Microsoft Office\Office13\MSAL.OCX Usually th...

Date Range on Report in Money 2003
I have a favorite report in Money 2003 which lists scheduled payments within a range of dates. My file was upgraded from Money 2000. I used to have the problem of the report omitting many of the scheduled transactions within the date range. That was fixed by running the Salvage program. Now the problem is the other way around: the report shows transactions BEYOND the date range, as well as those within. Is there a fix for that? I still have the Salvage program. :-) Thanks, Elliott ...

Insert date into file import
I have a database that imports a .csv file generated by anotherprogram. Unfortunately this program doesn't include a date in eachrecord in the .csv file.I can import the .csv file into my table alright, but I need to attach/insert a date (which I can get via VB code) into each importedrecord. Any ideas?At the moment I'm using the Docmd.TransferText command. have you tried importing the data, then running an Update query on thetable, to add a date to each record?hth<b_lwalker@hotmail.com> wrote in messagenews:1174264665.772034.212550@n59g2000hsh.googlegroups.com...> I have a ...

Copy/Paste macro...with a few twists
Hi, I needed to create a macro that would copy/paste all the selected rows into a new workbook which the macro creates. Before creating the workbook however I need it to ask the user for a six digit number, which it then takes and names the workbook (eg. PO######) and attaches the current date on the end. I'm still a newb to VBA so any help is appreciated. Best Regards, Mjack003 --- Message posted from http://www.ExcelForum.com/ Something like this might get you started: Option Explicit Sub testme() Dim myStr As String Dim newWkbk As Workbook Do myStr = ...

date format #32
i have a sheet where in some cells the date are in ddmmyy format along with text & in some cells mmddyy format along with text. is there any command to change all in mmddyy format On Sat, 20 Aug 2005 03:40:03 -0700, "Ankur" <Ankur@discussions.microsoft.com> wrote: >i have a sheet where in some cells the date are in ddmmyy format along with >text & in some cells mmddyy format along with text. > >is there any command to change all in mmddyy format You need to describe more precisely exactly what is in the cells, and how one can tell the difference betwee...

Excel default date format
1) The default date format for Excel is "21-Mar" when I enter date recognized data. This is unusable for me, I need the year displayed. How do I change the default date format to display the year. 2) With default also, Excel sometimes requires "=" in equations and sometimes not; i.e., if I enter a '-' it converts the cell to an equation. How do i stop this so that it only makes equations when I enter '='. 3) How do I get help on Excel inconsistancies like the above in Excel documentation anywhere? mjk #1. If I type in the date that includes the ye...

Macro to delete specific data in tables
Hi: I am an analyst working in service quality for a market research firm. We use SPSS 12 to output the data and later copy paste the tables into Excel for formatting and printing. The problem I currently have is that I got a large group of output tables which come in two different formats. The first table is like this: Satisfacción Expectativas Mean Std Deviation Masculino 5,4 1,4 Femenino 3,8 2,2 Group Total 5,3 1,5 Menos de 2400 UF 5,9 ,9 2401 a 25 mil UF 5,6 1,2 25.001 a 100 mil UF 5,0 1,6 Más de 100.001 UF ...

Macro Blunder
I seem to have lost the toolbar that pops up when you prompt Excel t create a new macro. The toolbar with the stop button and the relative reference button i gone. How do I get it back??? Thanks ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com nkob Tools>Customize>Toolbars. Select "Stop Recording" toolbar>Close Stop Recording Toolbar should now be visible on sheet. Hit "X" to close it(but not while you are recording). It will pop up next ...

how do i set an auto date function in excel or word?
I would like all documnts in both word and excel to automatically date as the document or report is prepared. =NOW() "hpkcommish" wrote: > I would like all documnts in both word and excel to automatically date as the > document or report is prepared. for excell Go to View> Header & footer >custom header footer then click on the clock pic & the date pic (choose 1st if you want it in the left middle or right feild) For word basically the same Cheers peterm "hpkcommish" wrote: > I would like all documnts in both word and excel to automatica...

SBS2008 - Activesync
Just put a whole bunch of users onto SBS2008 today and configured their mobiles. They really *hate* that their mobile handsets are locking after a short period of inactivity. Is it down to this policy setting ? Activesync - Maximum inactivity time lock 15mins They either want this lengthening considerably or want it disabling altogether. They want to choose when they want to lock their phones. What's the score regards this ? They want it to work the same as it did on their old SBS2003 server. Jim wrote: > Just put a whole bunch of users onto SBS2008 toda...

Embedded PDF object in web page: OneNote is launching and opening PDFs at the same time as Adobe Reader
I have a Windows 2008 Server machine with Office 2007, Adobe Reader 9, and IE8 installed. When opening a web page with an embedded PDF control (using <object> tag and Adobe Reader's CLASSID) in IE8, the PDF appears properly in the Adobe PDF control embedded in the web page, but at the same time, OneNote launches separately and opens the same PDF. How do we stop OneNote from doing this? We have XP Pro machines with Office 2007, Reader 9, IE8 that do not exhibit this behavior. -- Paul -------- Original-Nachricht -------- > I have a Windows 2008 Serv...

Problem with copying a to from date to another cell
I am trying to enter the first 7 days of the year in one cell. ie: A1 -> 1/2/05 to 1/8/05 And then I want to drag that down to 53 cells and have it copy in the following format. ie: A2 -> 1/9/05 to 1/15/05 A3 -> 1/16/05 to 1/22/05 etc... Is this possible? I would do it by using two separate columns, typing in the two top row dates, the highlight the area you wish to fill, then Edit > Fill > Series > Trend..........then when you have the two columns of dates, use a third helper column with this formula copied down...... =TEXT(A1,"mm/dd/yy")&...

Word 2007 Show Markup checkmarks and Date/Time Issue
Hi, I have a new HP desktop computer and finally got Word 2007. I have two questions. In the Review section of Word 2007, the Show Markup subsection has checkmarks for ink, comments, etc. as the default selection. Is there any way to change this default and uncheckmark them so it isn't automatic? I have looked everywhere online for a solution and can't find one. Second, in the bottom right-hand corner of my monitor, the time is showing as usual. However, when I first got my computer (last week), it also showed the date (I loved this feature). After re-starting my compute...

Date Format Problems
Is there anyway to make excel change the numbers I type in to the appropriate date? If I type 112603, how can I get the cell to display 11/26/03. I have tried formatting the cell, but it just doesn't seem to come out right. Any help would be greatly appreciated. Thank you Jennifer Only through VBA. See Chip Pearson's website. http://www.cpearson.com/excel/DateTimeEntry.htm Gord Dibben XL2002 On Fri, 5 Dec 2003 08:06:01 -0800, "Jennifer W" <icecream3@earthlink.net> wrote: >Is there anyway to make excel change the numbers I type in to the appropriate date?...

Open Form with macro and data from Selected Record
I am wanting a person to open a form, and have the subform display the records that relate to the main form. This part works. I then have a continuous form view for the subform and i want the user to be able to double click on the record they see in the subform and have it pull up that record into a blank form. I get the blank form to open up when they double click, however I cannot get the data for that record to show up. I either get a blank/new record or all records. I am trying the SearchForRecord macro in an On Load Event. This loads after the form has been opened. Ac...

numbering macro
In a database of say 10 columns and 100 rows, how do I record a macro that will o Insert a new column to the left of column A, then o in the new column A, number the rows that contain data in column B. Note: The numbering should start from 1. Since row 1 has the database heading & row 2 has the column headings, the numbering (no. 1) should start from row 3 and continue down to the last row that has data in column B. Columns("A:A").Insert For i = 1 to 100 If Cells(i,"B").Value <> "" Then Cells(i,"A&q...

Problem formatting date values in chart...
I have a blood pressure tracking spreadsheet -- at a minimum, it tracks Systolic and Diastolic pressure for a given day. A B C E.g.: Date Systolic Diastolic 1 9/3/04 120 90 2 9/4/04 123 87 3 9/5/04 129 79 I currently have a simple line graph that shows systolic data points over diastolic data points. The left hand (Y axis) value is "Pressure". My problem is that I want the "date" values of Col. A to display at the bottom of the graph as a label with each corresponding data point and...