Should be an easy but I can not figure it out!!

I have a text box where the user enters a date and it is assigned to variable 
txtDate.  How to I convert that to the serial number of the date?

Thanks for the help
0
Utf
11/16/2009 2:01:07 PM
excel.programming 6508 articles. 2 followers. Follow

12 Replies
3141 Views

Similar Articles

[PageSpeed] 12

IIRC, you can use

DATEVAL(String) to get the serial date.
-- 
HTH,

Barb Reinhardt



"VinceW" wrote:

> I have a text box where the user enters a date and it is assigned to variable 
> txtDate.  How to I convert that to the serial number of the date?
> 
> Thanks for the help
0
Utf
11/16/2009 2:04:01 PM
Barb,
Thanks for the quick reply, however I get the error "Sub or Function not 
defined" when trying that.  Any suggestions?

"Barb Reinhardt" wrote:

> IIRC, you can use
> 
> DATEVAL(String) to get the serial date.
> -- 
> HTH,
> 
> Barb Reinhardt
> 
> 
> 
> "VinceW" wrote:
> 
> > I have a text box where the user enters a date and it is assigned to variable 
> > txtDate.  How to I convert that to the serial number of the date?
> > 
> > Thanks for the help
0
Utf
11/16/2009 2:13:03 PM
My apologies.  It's DateValue.
-- 
HTH,

Barb Reinhardt



"VinceW" wrote:

> Barb,
> Thanks for the quick reply, however I get the error "Sub or Function not 
> defined" when trying that.  Any suggestions?
> 
> "Barb Reinhardt" wrote:
> 
> > IIRC, you can use
> > 
> > DATEVAL(String) to get the serial date.
> > -- 
> > HTH,
> > 
> > Barb Reinhardt
> > 
> > 
> > 
> > "VinceW" wrote:
> > 
> > > I have a text box where the user enters a date and it is assigned to variable 
> > > txtDate.  How to I convert that to the serial number of the date?
> > > 
> > > Thanks for the help
0
Utf
11/16/2009 2:17:01 PM
I started with DateValue before searching for an answer here, thinking that 
would do it.  I have a breakpoint so I can see the value of 
DateValue(txtDate) and it shows me the date of 11/16/2008 rather than the 
serial number.  I am really confused on this one.  Thanks for your assistance 
and any further suggestions.

"Barb Reinhardt" wrote:

> My apologies.  It's DateValue.
> -- 
> HTH,
> 
> Barb Reinhardt
> 
> 
> 
> "VinceW" wrote:
> 
> > Barb,
> > Thanks for the quick reply, however I get the error "Sub or Function not 
> > defined" when trying that.  Any suggestions?
> > 
> > "Barb Reinhardt" wrote:
> > 
> > > IIRC, you can use
> > > 
> > > DATEVAL(String) to get the serial date.
> > > -- 
> > > HTH,
> > > 
> > > Barb Reinhardt
> > > 
> > > 
> > > 
> > > "VinceW" wrote:
> > > 
> > > > I have a text box where the user enters a date and it is assigned to variable 
> > > > txtDate.  How to I convert that to the serial number of the date?
> > > > 
> > > > Thanks for the help
0
Utf
11/16/2009 2:24:08 PM

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"VinceW" <VinceW@discussions.microsoft.com> wrote in message 
news:7D68FA40-E076-467B-B870-71A950D74543@microsoft.com...
>I have a text box where the user enters a date and it is assigned to 
>variable
> txtDate.  How to I convert that to the serial number of the date?
>
> Thanks for the help 

0
Don
11/16/2009 2:33:07 PM
text box ?? Do you mean inputbox?

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"VinceW" <VinceW@discussions.microsoft.com> wrote in message 
news:7D68FA40-E076-467B-B870-71A950D74543@microsoft.com...
>I have a text box where the user enters a date and it is assigned to 
>variable
> txtDate.  How to I convert that to the serial number of the date?
>
> Thanks for the help 

0
Don
11/16/2009 2:33:28 PM
Barb.....Oh my what a simple problem this really was.  The DateValue is the 
correct function to use.  Although the date is shown as 11/16/2009 and the 
code puts that in the cell requested, I simply changed the format of the cell 
to be numeric ans the serial number 40133 is there just as it should be!!!!  
Sometimes it is the forest and the trees syndrome!!.... Thanks for your help 
and suggestions.

"Barb Reinhardt" wrote:

> My apologies.  It's DateValue.
> -- 
> HTH,
> 
> Barb Reinhardt
> 
> 
> 
> "VinceW" wrote:
> 
> > Barb,
> > Thanks for the quick reply, however I get the error "Sub or Function not 
> > defined" when trying that.  Any suggestions?
> > 
> > "Barb Reinhardt" wrote:
> > 
> > > IIRC, you can use
> > > 
> > > DATEVAL(String) to get the serial date.
> > > -- 
> > > HTH,
> > > 
> > > Barb Reinhardt
> > > 
> > > 
> > > 
> > > "VinceW" wrote:
> > > 
> > > > I have a text box where the user enters a date and it is assigned to variable 
> > > > txtDate.  How to I convert that to the serial number of the date?
> > > > 
> > > > Thanks for the help
0
Utf
11/16/2009 2:36:06 PM
On Mon, 16 Nov 2009 06:01:07 -0800, VinceW <VinceW@discussions.microsoft.com>
wrote:

>I have a text box where the user enters a date and it is assigned to variable 
>txtDate.  How to I convert that to the serial number of the date?
>
>Thanks for the help

One question I have is "what do you mean by 'the serial number of the date'"?

MS stores dates using serial numbers where 1 = 1 Jan 1900 (depending on which
date system you are using).

If txtDate is a string, then to show the "serial number" you could use
something like:

clng(datevalue(txtdate))

If you mean something else by "serial number of the date", please be more
specific.

--ron
0
Ron
11/16/2009 2:45:41 PM
Don,
I am using a text box on a user form for the user to enter their date.  
Could not figure out how to convert the date string to a serial number.  
However the DateValue function does work as long as the cell in the 
spreadsheet is formatted as numeric.

Thanks for the reply


"Don Guillett" wrote:

> 
> 
> -- 
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett1@austin.rr.com
> "VinceW" <VinceW@discussions.microsoft.com> wrote in message 
> news:7D68FA40-E076-467B-B870-71A950D74543@microsoft.com...
> >I have a text box where the user enters a date and it is assigned to 
> >variable
> > txtDate.  How to I convert that to the serial number of the date?
> >
> > Thanks for the help 
> 
> .
> 
0
Utf
11/16/2009 3:24:01 PM
If you don't want to always change the date to a numeric value, you can use 
this to get the serial number...

SerialNumber = CDbl(CDate(Range("A1").Value))

Just change the cell reference as needed. Of course, the Range("A1").Value 
part could be replaced by any String value if needed (say, from a TextBox as 
an example).

-- 
Rick (MVP - Excel)


"VinceW" <VinceW@discussions.microsoft.com> wrote in message 
news:F7F509BA-5D01-4F32-9657-05028114FE8E@microsoft.com...
> Barb.....Oh my what a simple problem this really was.  The DateValue is 
> the
> correct function to use.  Although the date is shown as 11/16/2009 and the
> code puts that in the cell requested, I simply changed the format of the 
> cell
> to be numeric ans the serial number 40133 is there just as it should 
> be!!!!
> Sometimes it is the forest and the trees syndrome!!.... Thanks for your 
> help
> and suggestions.
>
> "Barb Reinhardt" wrote:
>
>> My apologies.  It's DateValue.
>> -- 
>> HTH,
>>
>> Barb Reinhardt
>>
>>
>>
>> "VinceW" wrote:
>>
>> > Barb,
>> > Thanks for the quick reply, however I get the error "Sub or Function 
>> > not
>> > defined" when trying that.  Any suggestions?
>> >
>> > "Barb Reinhardt" wrote:
>> >
>> > > IIRC, you can use
>> > >
>> > > DATEVAL(String) to get the serial date.
>> > > -- 
>> > > HTH,
>> > >
>> > > Barb Reinhardt
>> > >
>> > >
>> > >
>> > > "VinceW" wrote:
>> > >
>> > > > I have a text box where the user enters a date and it is assigned 
>> > > > to variable
>> > > > txtDate.  How to I convert that to the serial number of the date?
>> > > >
>> > > > Thanks for the help 

0
Rick
11/16/2009 3:41:04 PM
Ron,
Thanks again for another very simple solution to what I was looking for.  
That also works fine.  Ever have one of those days where the brain turns a 
problem into something more difficult than it really is!

Thanks for your reply.

"Ron Rosenfeld" wrote:

> On Mon, 16 Nov 2009 06:01:07 -0800, VinceW <VinceW@discussions.microsoft.com>
> wrote:
> 
> >I have a text box where the user enters a date and it is assigned to variable 
> >txtDate.  How to I convert that to the serial number of the date?
> >
> >Thanks for the help
> 
> One question I have is "what do you mean by 'the serial number of the date'"?
> 
> MS stores dates using serial numbers where 1 = 1 Jan 1900 (depending on which
> date system you are using).
> 
> If txtDate is a string, then to show the "serial number" you could use
> something like:
> 
> clng(datevalue(txtdate))
> 
> If you mean something else by "serial number of the date", please be more
> specific.
> 
> --ron
> .
> 
0
Utf
11/16/2009 4:37:01 PM
On Mon, 16 Nov 2009 08:37:01 -0800, VinceW <VinceW@discussions.microsoft.com>
wrote:

>Ron,
>Thanks again for another very simple solution to what I was looking for.  
>That also works fine.  Ever have one of those days where the brain turns a 
>problem into something more difficult than it really is!
>
>Thanks for your reply.

You're welcome.  Glad to help.  And yes, I do have those days; often followed
by hours of kicking myself over the time I've lost!
--ron
0
Ron
11/16/2009 8:42:03 PM
Reply:

Similar Artilces:

Can't close a personal folder
To retrieve an old email, I placed a backup .pst file on my desktop and opened it from within Outlook 2003. I found the message I wanted and then deleted the .pst file before closing it from within Outlook. Now I can't "close" the folder. If I right-click on it (in the list under "All Mail Folders") and click "Close" from the context menu, I get an error message: "The operation failed. An object could not be found." If i left-click on it, the error message is "The set of folders could not be opened." If I click on the +, it's "Un...

How can I create such a control
I want to create a custom control. It can be laid on dialog and formview, have standard horizontal and vertical scroll bar, and supports drawing, print preview and printing I have tried to use a class deriving from CScrollView. But when I use it in formview, the program cann't run I have also tried a class deriving from CWnd, it seems that it works. But I don't know how to deal with scroll bar Thank you! fengxvhui, CWnd is what I would have chosen. You must handle WM_HSCROLL and WM_VSCROLL. First of all, you must set up the scroll bar, so: SCROLLINFO si; si.cbSize = sizeof( SCROL...

Can't insert object
I am trying to create a chart in an Excel 2002 worksheet, but as I go thru the chart wizard the "As object in" option is turned off. It will only create a chart as a new sheet. When I click on Insert Obect, I get an error message "Can't insert object". Does anyone know how to fix this? Thanks, Steve Steve Is the worksheet protected? -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS <nedrgr21@yahoo.com> wrote in message news:i2eev0t5cebvl4g5ol8c6mfmdi9c89a362@4ax.com... >I am trying to create a chart in ...

Can't save older file / Compatability Report
I have some older files created with Word 6.0/95 versions on a PC and when I open them, modify them and try to save them, they won't save without changing the name. I tried trying turning off the "check documents for compatibility" button, but it still won't let me save them. I haven't had anything running Word 6.0 in years, and will never try to open them in that format again, but why can't I get the Mac to just over-write to a new file format? The "help" files and explainations of the "compatability report" are as unhelpful as I have come to e...

Calendar easy to move from computer to computer?
Is there a way besides a Palm pilot to transfer all contacts, and calendar info from work to the home computer as a daily task? The problem is my palm pilot does not carry over certain fields in contacts that transfer from work to home. Is there a web based outlook? In Calendar, the color labeling does not transfer either. Looking for an easy way to have both computers updated in the same way. Thanks! .. ...

How can I foward emails with high importance
Need some help with a rule I would like to forward a copy of an email after it arrives marked high importance to another address and also save the email in a specific folder I've tried several time with no success any Idea's Thanks Which version of Outlook do you have, what message store do you use and what criteria did you set? Here are some tips about how to post questions: http://support.microsoft.com/?id=555375 Regards Judy Gleeson MVP Outlook Trainer and Consultant There are various articles about using Outlook here: www.judygleeson.com Canberra, Australia "tire...

Can't import more than 50 records
I've spent the past few days trying (with no luck) to import my credit card statement into Money 2004. For some reason, even though Money says it imported 87 transactions, only 50 new transactions appear in the account. I've tried deleting the statement and re-importing. I've tried importing both ofx and qif statements, and tried importing different months (I'm a couple months behind). I've repaired my Money file. I've even archived some older transactions to see if that helped. Nothing works. I'm just about ready to uninstall Money 2004 and go back to 2003. Any ...

easy OnLButtonDblClk() question
Hi, I've created a MFC application. It has the standard window where information is displayed. I have recently copied some code from another applcation someone made for me, so that I have a dockable window that attaches to the main document. The code for this class it as follows: class CMarkerZoom : public CWnd { // Construction public: CMarkerZoom(); // Overrides // ClassWizard generated virtual function overrides file://{{AFX_VIRTUAL(CMarkerZoom) file://}}AFX_VIRTUAL // Implementation public: virtual ~CMarkerZoom(); // Generated message map functions protected: file://{{AFX_...

Can't open file in email & explorer, but excel opens
When I double click on a file in email or explorer, excel will open but the file will not. This just started recently. Hi ralegr Try Tools>Options>General uncheck "Ignore other Applications" Exit Excel and try again If this doesn't work try to re-register Excel Close Excel first and On the Windows Taskbar 1 ) Start>Run "excel.exe /unregserver"(no quotes)>OK. 2) Start>Run "excel.exe /regserver"(no quotes)>OK. See the space between exe and /regserver You might have to designate a full path to excel.exe. In that case Start>Run "...

rca easy rip
i have installed rca easy rip on my windows xp media center computor. i wanted to remove it, but when i go to remove all programs and try to remoe, it will not remove. any suggestions on how to get this program removed? You would probably find more help and knowledge at the support site for that application, but you might try reinstalling and then uninstalling. The RCA support site mentioned the need to have the RCA player connected to the computer when installing, perhaps it needs to be connected at uninstall. http://voxrightnow.custhelp.com/app/answers/detail/a_id/1773/kw/uni...

Calendar View
All, My sales team currently uses exchange - and would like to become more in tune with each other's availability and what not. Currently, the only way to view another's calendar is to go throught the steps of creating an appointment, selecting members, then viewing which times are available. Is there an add in that will allow you to view calendars without going through the steps above? A quick view sort of thing? Thanks in advance! Chris I have seen something u can do with sharepoint. where you create a sharepoint site. put you vacation time in that. then, in outlook you c...

easy transfer
I want to use easy transfer from my XP to a new PC with Windows 7. Can I just transfer what is on my old PC to a flash drive then just plug the flash drive into the new PC and then start the easy transfer on the new PC and just get the information off of the flash drive? Mike Givens wrote: > I want to use easy transfer from my XP to a new PC with Windows 7. > Can I just transfer what is on my old PC to a flash drive then just plug > the flash drive into the new PC and then start the easy transfer on the > new PC and just get the information off of the fla...

An easy one for you pros !
How to disable the right click of the mouse on an open form and report ? Or for that matter in all of my application. Thank you once again. -- Eric "ericb" <eric@b.com> wrote in message news:17826C24-3B6B-4707-81BF-9EA7C9299AB4@microsoft.com... > How to disable the right click of the mouse on an open form and report ? There's a Shortcut Menu property on the Other tab of a form's property sheet in design view. It can be set to No to disable shortcut menus. That may be what you're looking for, for forms. I'm not sure about reports. --...

can comments be turned off
can comments be turned off...so that they don't show unless you want them to? thanks. Tools > Options > View gives you 3 choices. Also see DisplayCommentIndicator Property in Excel's Help. example of code: Dim Cmt As Comment For Each Cmt In ActiveSheet.Comments Cmt.Visible = False Next etc yes. select Tools, Options, View tab, then change the setting for comments to whatever you want- the indicator only will show a red triangle in the cell with a comment smokiibear@gmail.com wrote: >can comments be turned off...so that they don't show unless you want >...

Can Integration Manager accept negative value?
Dear Sir, We found that amount of the cash receipts cannot be negative. Is that true? How can we handle it if we need to enter the negative figure? Thanks. Best regards, Edward Since Great Plains will not allow you to enter a negative cash receipt, neither will Integration Manager. Depending on exactly what you're trying to achieve with a negative cash receipt, a debit memo may be what you need. -- Victoria Yudin GP MVP "Edward" <Edward@discussions.microsoft.com> wrote in message news:1C282EC2-8198-4E69-BE46-50A439EF4624@microsoft.com... > Dear Sir, > >...

Easy birthdays question...
Hi, I have one table in my database named, Clients. It has the following fields: FirstName, Surname, Address1, Address2, Address3, DOB, and LetterSent. My question is, how do I show all the clients who have a birthday coming up in the next 7 days? Once I have that information displayed, the user is then supposed to put a tick the LetterSent checkbox for each client. Then, say the query was run later that day, the clients who appeared in the query earlier on would now not appear as they have a tick in the LetterSent field. So I think I need something like this, I just don't know t...

CImageList::DragEnter() can't lock window
Hi all, I'm implementing a drag & drop function for my tree control in VC6. Something I cannot understood happened here: according to the documentation, DragEnter() should "lock updates to the window specified by pWndLock". But actually, it did not. One reason to say so is that the window is still updating unexpectly, and, LockWindowUpdate() still returned TRUE after call to DragEnter(). Any suggestions? Thanks in advance. "Victor" <vicchina@21cn.com> wrote in message news:e1ASPHdwDHA.2396@TK2MSFTNGP09.phx.gbl... > Hi all, > > I'm implementin...

Can Not Start MSOutlook Unable to Display The Selected Folder or Item
MSOutlook Unable to Display The Selected Folder or Item. The Info Store Could not be Opened. This is the error that my end user is receiving when he attempts to work offline. He can connect to the server, synch just fine and have full functionality. When he attempts to work offline he resceives this message with a big red X. He was able to connect offline up until last week. The only change I could think of was an automatic update. Any suggestions? Have you tried deleting his .ost and having Outlook recreate one? How big is the .ost file? What version of OUtlook/Exchange are y...

Can't access public folders
My company currently runs in an Exchange 5.5 environment and we are working on getting migrated to Exchange 2003. One of the problems that we are facing, though, is that after moving a mailbox to the 2003 server, that user can no longer access the Public Folders. I used pfMigrate.wsf to setup the replicated folders on the 2003 server, and you can see the contents from OWA, but cannot access them through the Outlook 2003 client. I verified that the folder tree type is MAPI clients. If any other information is needed, let me know...the error message you receive is Unable to display fo...

can i do this type of stacking column chart? (and how?)
as a non-mathematically-minded designer type, i could really use some help! i need to create some stacking column charts where: - the value axis represents money, in a particular scale, in particular increments - the x axis represents different companies - each bar represents the MINIMUM, MIDPOINT and MAXIMUM amounts of money spent by each company, graphed against the value scale of money For example, say the scale is $1 to $100 in $10 increments. Company A spends a minimum of $5, a midpoint of $50 and a maximum of $70. I need the bar in the graph for company A to have three color segment...

Where can I find poolmon.exe?
Hello, I need to run the tool called poolmon.exe, I have installed the Windows 2003 support tools and it doesn't appear to be in the list, any ideas? Whiteford wrote: > Hello, > > I need to run the tool called poolmon.exe, I have installed the Windows > 2003 support tools and it doesn't appear to be in the list, any ideas? http://www.microsoft.com/downloads/details.aspx?FamilyId=49AE8576-9BB9-4126-9761-BA8011FABF38&displaylang=en John "Whiteford" <no@no.com> wrote in message news:eYPpklDHLHA.5700@TK2MSFTNGP04.phx.gbl... >...

about some easy drawing
Hi! Nothing is being drawn when I run this piece of code ? I can't see any wrong in the code but there must be some mistake in it. private void Form1_Paint(object sender, PaintEventArgs e) { Pen p = new Pen(Color.Red, 7); p.DashStyle = DashStyle.Dot; Bitmap bm = new Bitmap(400,400); Graphics g = Graphics.FromImage(bm); g.DrawPie(p,0,0,350,350,290,90); } //Tony "Tony Johansson" <johansson.andersson@telia.com> wrote in message news:OxZriJf%23KHA.5464@TK2MSFTNGP05.phx.gbl... > Nothing is bei...

Can't find bad addresses to delete
I send out emails to a personal group of names, but a number of them = kick=20 back, no one by that name, etc. Despite many attempts to find the bad = email=20 addresses in my personal address book, they aren't there, so I can't = figure=20 out how to delete these invisible baddies. Anyone know how? I tried to export the address book, in hopes a text file would yield the = missing names, but it would only export the contacts list, which is not=20 where I keep addresses. MS sites have proven useless in my quest for help. Thanks, and if you have the answer or ideas, please email me d...

How can I open filedialog
I try copy this code from Northwind to my form. Dim dlgOpen As FileDialog Set dlgOpen = Application.FileDialog( _ FileDialogType:=msoFileDialogOpen) With dlgOpen .AllowMultiSelect = True .Show End With when I click button it show me "User defined type not defined" What should I do? I generally use the code from http://www.mvps.org/access/api/api0001.htm. It has always worked. They also have code on the site to browse for a folder. -- Duane Hookom Microsoft Access MVP "Nova" wrote: > I try copy this code from Northwind...

I can login as 'sa' but not as any other user
I created a UserID on my workstation and cannot log in as that user to test it. I haven't been able to log in with my own userid in ages so I always use 'sa'. I even went so far as to uninstall GP, delete the programs folder, and reinstall everything. Any ideas? thanks. Hi Barbola Have you tried running the Grant script against your company Dynamics DB's? That should work for you. Also what version of GP are you running? The default path to the script (V10) is C:\Program Files\Microsoft Dynamics\GP\SQL\Util Hope it helps. -- Fliehigh "barbola" wrote:...