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
5230 Views

Similar Articles

[PageSpeed] 1

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 print email messages
Can't print any email messages from Outlook Express 6. Can print from all other Windows apps. Error Message: -------------------------------- Internet Explorer Script Error Line: 1344 Char: 1 Error: Unspecified error. Code: 0 URL: res://C:\WINNT\system32\shdoclc.dll/preview.dlg Tim Girvin <tagirvin@NO_SPAMhotmail.com> wrote: > Can't print any email messages from Outlook Express 6. Can print from > all other Windows apps. Ask in an Outlook Express newsgroup. -- Brian Tillman Sorry, but I forgot to mention that we imported email to Outlook and it and the same problem...

how can i put graphs on one sheet
I want to draw a three lines with their coordinated on one graph sheet Please explain this question in more detail. -- Thanks, Shane Devenshire "Hass" wrote: > I want to draw a three lines with their coordinated on one graph sheet > ...

Can I make a single tab disabled instead of the whole tab control?
For example, a tab control has 3 pages(tabs), and what I want to do is to make the first page(tab) disabled. Thanks for any help! The way I handle this in my application is like the following. This does not disable the tab, but it removes the text. Then I have other code in the property sheet that detects if the disabled tab is selected and makes the next tab active instead. // in the property sheet class CTabCtrl* pTab = GetTabControl(); ASSERT (pTab); TC_ITEM ti; char szText[2]; ti.mask = TCIF_TEXT; ti.pszText = szText; ti.cchTextMax = 1; VERIFY( pTab->GetItem( nPage, &ti)); st...

Figures in data box and percentages in graph
Hi - I have a client for whom we have developed a report system. They now want an ammendment to some of the graphs which means that they want to be able to see the actual figures in the data box under the graph and the bars/lines in the body of the graph to be based on percentages. Does anyone have any ideas on this? Regards Andrew You need to fake the data table. Tushar Mehta has a discussion of data tables and alternatives on his web site, http://tushar-mehta.com. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://Pelti...

Messenger 80040154 error
I've tried all as suggested here... http://help.live.com/help.aspx?mkt=en-us&project=WL_Messengerv9&querytype=keyword&query=80040154&Product=2&Version=14.0.8089.0726&ErrorCode=80040154&ErrorId=80040154&Locale=en_ms ....still can't sign in. Please help. Please! I know many are having this issue, so I hope for a quick fix if someone can jump in. Thanks "Ashley Smith" wrote: > I've tried all as suggested here... > > http://help.live.com/help.aspx?mkt=en-us&project=WL_Messengerv9&querytype=keyword&am...

can a form display current table field names?
Hello, I need to create a form that will display the current field names resulting from a make-table querythat can change from audit to audit. This way the user is always aware of the "current" field names in their dataset. Currently, a form displays the structure from a linked (text formt) table that I have to manually export every time the make-table query changes and I'd like this to be generated from a macro or from code. Any Ideas? I thank you, John_Lehmus Maine, USA This article illustrates how you can list the field names, types, and descriptions for a table: ...

Can't log on without activation
I have two machines in he shop which had to have XP repaired. They both have XP Home SP3, with legit COA’s. Unfortunately, when I get to the logon screen, it tells me that this copy of XP must be activated before I can log on – do I want to activate now? Well, yes, of course I do. But, when I say Yes, I just get the desktop wallpaper – no icons, machine stops there, nothing I can do but switch off. So I tried Safe Mode... “You can’t log on without activating. You can’t activate in Safe Mode. Please now slit your wrists”. What can I do please? I have two customers who are ge...

How can I tell how many computers my Office product is good for?
I have MS Office Professional Plus 2007. Where can I find out how many computers I can load it onto? And if the answer is just one, how can I appropriately remove it from the laptop and put it on the desktop? Thank you. Hi Carl, If it's an OEM version, the license terms limit the installation to the device on which the software was pre-installed. If it's a retail version, you can install MS Office Professional Plus 2007 on one desktop PC and one laptop, or on two laptops. -- Cheers macropod [Microsoft MVP - Word] "Carl" <Carl@discussions.mic...

money 2004 can't login
MS Money2004 under XP. The programme has suddenly stopped me from signing in as previously. When I ask to use Money's online features to login, I receive a message that the attempt was unsuccessful. When I ask to sign in without online features, I am told the password is incorrect. When I click on "Forgotten your password", I reach the following website--https://member services.passport. com/ppsecure/MRSV_ResetPW.asp which Internet Explorer cannot display. Any advice as to how to overcome this problem would be most appreciated. -- gw In microsoft.public.money, GrahamW wrot...

CRM Online, can't convert Lead, how do you find real cause or erro
I get generic error message when a CEO role tries to convert a lead. We are using CRM Online, is there any way to get more descriptive error, log file, etc? We just see message box that says: "The logged-on user does not have the appropriate security permissions to view these records or perform the specific action." This person was CEO role for the parent Business Unit and should have no probs converting a lead to an account or contact. Anyone have any ideas on this? Can MS support provide error logs for your online domain? thanks! ...

How can i track re-assigned contacts
I need help to keep track of re-assigned contacts in the CRM.Any help is greatly appreciated -- IT Manager By default when you assign a contact to some one else you automatically are assigned rights to the specific contact. You should still have access to those contacts. But what exactly do you mean with tracking a contact. -- Patrick Verbeeten (MCPD) Lead Developer Aviva IT Web: http://www.patrickverbeeten.com/maps/technical.aspx Web: http://www.aviva-it.nl "Melvin F" wrote: > I need help to keep track of re-assigned contacts in the CRM.Any help is > greatly appr...

can I do this in xsl
Quick question: I have some text containing strings to strip out and replace: eg. "some text some text some %replacethis% text some text some text" with the % flagging what to replace. I can substring-before upto the first %, then I try to get the rest of the string after the 2nd % using: <xsl:value-of select="substring-after(substring-after($theText, $replaceFlag), $replaceFlag)"/> to pass as a parameter when I then recursively call my template to replace the next occurrence. but this doesnt seem to work. I am new to xsl and i am finding the fact that you ...

cannot connect to server, can't find server
help!!! I started RMS from scratch did everything you need to do to start using RMS but then after a few months i started up my PC and cannot connect to a server ( it is a local server only one machine) Now can't use the POS or Manager let alone use the administrator...please help... Look at the botton left hand corner of your computer screen in the System Tray next to were you clock is, there should be an icon there that is called somthing like this: Running - \\SomeName - MSSQLServer and it looks like a Tower PC with a little white circle and inside the circle there should be a ...

Can you make a scatter graft
I am wanting to make a scatter graft or dot graft but do not know how Put your X values into one column, and your Y values into the next column, and if you want to name the series of points, put the label into the cell just above the first Y value. Select the data, and run the chart wizard (the little chart icon on the standard toolbar, or go to Insert menu > Chart). In step one, choose an XY Scatter type, then continue through the wizard. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Leanne ...

Why can't a range formula be on a different row?
I name cells A2:A4 Range1. I name cells B2:B4 Range2. In C2:C4 I type =Range1-Range2 I get the right answer. But if I move down a couple of rows and in A9:A11 I type =Range1-Range2 I get a #Value error. Can anyone explain why? Evi Evi, My guess is that you're getting only once cell from range1 and one from range2 in your result. You're asking the formula to subtract a range from a range, and return a single result. That results in only one value from each range, if the formula is in the same row. You may want =SUM(Range1)-SUM(Range2) That will work anywhere. -- Earl Kioster...

How can I make my site fill the browser height
How can I make my site fill the browser height. As it is now it fills the browser's width. I repeat a 1-pixel vertical line to fill-in the sides. I put a border about the html and the body and I see they both end at the bottom of my footer image. I think I need to extend the body to the bottom of the browser. I'd like to color the browser area below the footer to match my scheme. Got any suggestions?? Thank " Cal Who" <CalWhoNOSPAM@roadrunner.com> wrote in message news:htuqjm$e70$1@news.eternal-september.org... > How can I mak...

object can not be found #2
Hi, I am trying to restore Outlook 2002 to default. The reason is when I click on properties/ advanced, of my top level folder, I get an error message "object cant be found". I have reinstalled XP previously, and when I reinstalled Outlook, I have been importing from my backed up pst files. I have tried reinstalling Office XP, and find that it keeps asking for a pst that was previously default. This is ok, but when I go to properties/ advanced, I get the "object cant be found" error. How can I reinstall office XP, and bring up a fresh default folder, so I can import from ...

how can i modify shape tooltip and retain hyperlink #2
I have a custom shape and want to display a custom property in the tooltip. I've done this by editing miscellaneous.comment and I also had to set the shape hyperlink visibility to false. But that means the hyperlink is no longer available in the pop-up window. Is there any way i can have both the tooltip i want and also a hyperlink in the pop-up window? ...

can I copy photos attached to emails onto a disc
I receive lots of emails with attached photos. How do I get the photos from the inbox of Microsoft Outlook onto a disc so that I can get them printed ????? ...

can I use windows live as my default email program?
I don't want to use Outlook as my default email program. am I able to change the default setting? Thanks. You posted to a newsgroup that supports the use of Microsoft Access, a relational database. You'll probably have more luck getting a response if you post to a Windows or an Outlook newsgroup. (you might try using the Tools/Internet Options tab of your internet browser software. Some versions allow you to specify your default email program there.) Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and servi...

How can I stop repeatint information from appearing in my report
I am tracking Clearances for Foster Parents & their families. My queries work great (thanks, by the way to those that helped!!). I am now trying to create a report. Everything works great EXCEPT for the fact that I have repeating data appearing. I have the Foster Parent (FP) info and the Spousal information in the Header section of my report. In the DETAIL section I have the REsidents int he home (there can be many residents to one Foster Parent). Here is the problem. Becaue the REsident info is relating to BOTH the Spouse and FP info it is repeating TWICE. But if I put the Spousa...

Physical inventory Can we Change This??
Hi, I have a little problem. I have a hand held scanner (scanpal 2) and it works great with RMS. but when I, import the file . it doesn't find the Items and is Because is looking for the Item lookup Code and not the Aliases.(UPC). how can I change or ad on the field type (Field matching) the "Aliases". thanks jose -- Reynold Cycle This is going to take you a few minutes... From Store Ops Admin, run the following query: SELECT ItemID, Alias FROM Alias Export the result to a .csv file. If you have any alias (and you probably do) that are all numeric and start with a z...

how can I delete name
Hi, How can I delete or change the name that appears at the top of any email that I am trying to print out. I have outlook 2003. Cheers Lenny ...

can't pull any reports or edit any access files
This is the second copy of Office 2003 that I have purchased. As with the first copy I can not edit any of the databases that came with my college class...nor can I get any reports to pull up. Each time I try the program gives me an error message that says an error has occurred and access has to close. Can anyone help me with this???? Thanks! Amanda hi Amanda, mkspropps wrote: > This is the second copy of Office 2003 that I have purchased. As with the > first copy I can not edit any of the databases that came with my college > class...nor can I get any reports to pull up. Eac...

How can I bybass sending to the Junk E-mail folder using IMF.
We would like any suspect messages to be sent to the users Inbox and not the Junk E-mail folder. Is there a way to do this? Also, what's the most popular scl settings? We're using 8 for the gateway setting and and 7 for the junk e-mail setting. -- tigerfanlsu On Mon, 18 Jul 2005 09:30:23 -0700, "kk" <kk@discussions.microsoft.com> hired a team of monkeys to write: >We would like any suspect messages to be sent to the users Inbox and not the >Junk E-mail folder. Is there a way to do this? >Also, what's the most popular scl settings? We're using...