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

Similar Articles

[PageSpeed] 59

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:

How can I take back a licence
I assigned a licence to a user that no longer uses CRM, How can I take back the licence? Does anyone know Thanks, CC Sorry about my first reply I clicked Send to quickly If you go into Settings - Business Unit Settings - User Doulbe click the required use With the user details form open goto the Licenses sideta Actions menu - Manage License Select the License and click << to remove Dont forget to assign his data to another user before disabling him. "Martin Gallagher" <martin.gallagher@pygmalion.com.nospam> wrote in message news:664EC32F-0E49-4975-AA33-D8CE32...

Can I prevent a spreadsheet from being copied?
I'm going to publish a retirement nest egg calculator to the web and want to know if I can prevent buyers from copying it, or if I can restrict them to being only able to use it on one computer. I plan to supply any purcashed copies by email. Thanks, George Forbes Hi George This needs a lot of creative, part evil, VBA programming, and you must ensure that macros are enabled for the file to work. Very complicated. HTH. Best wishes Harald "Nest Egg George" <Nest Egg George@discussions.microsoft.com> wrote in message news:C8BA275E-BCE9-4748-85B2-1A0199...

can't restore mailbox
Hello, I am running Exchange 2003 and need to restore a single users mailbox who no longer has an account/mailbox in Active Directory (there account was deleted a few months ago). I was able to get as far as restoring and mounting the datastore that has the users mailbox, but when I try using the 'recover mailbox data' option in the exchange tasks, I get this error: The information store could not be opened. The logon to the Microsoft Exchange Server computer failed. MAPI 1.0 ID no: 80040111-0286-00000000 . I see the mailbox I want to restore and everything, I just can'...

maximum number of rows that can be coppied to clipboard
is there a maximum number of rows that can be copied to clipboard from a table? using access 2002 on XP, Roland wrote: > is there a maximum number of rows that can be copied to clipboard from a > table? using access 2002 on XP, I have Access 2003 when I tried to copy a table with 70,000 records it said the limit was about 65,000. gls858 65536 -- Regards - Andy Wiggins FCCA www.BygSoftware.com Excel, Access and VBA Consultancy "Roland" <Roland@discussions.microsoft.com> wrote in message news:CF98F875-56C6-4C22-8695-D8CFBE47D12E@microsoft.com... > is there a...

Can't see existing table in Design view for a QBE
Hi Guys, I can't see a table in QBE even when I have the scroll bar as far left as I possibly can. I have joineed another table to the query and I can see the relationship link but am unable to scroll left to view the table. Does anyone know how to fix this?? Many thanks in advance, Cheers, On Tue, 18 May 2010 17:35:01 -0700, DontKnow <DontKnow@discussions.microsoft.com> wrote: >Hi Guys, > >I can't see a table in QBE even when I have the scroll bar as far left as I >possibly can. I have joineed another table to the query and I can see the ...

Can you sort text in a column, but leave cell color alone?
1st time using excel.... I want to make a wrestling roster to keep the wrestlers weights for each day. I would like to have a wrestler's name and weight of each day in a row. To make it easier to see, I would like for every other row to have a grey shade (like stripes). When I sort, the shade goes with the text. Is there anyway to make a shade on each other row and still be able to sort? Hi yes, select your worksheet and choose format / conditional formatting choose formula is type =ROW()=ODD(ROW()) click on the format button, go to the patterns tab, choose a fill colour click OK tw...

Can't re-enable microphone array
Folks- I run run Vista Home Premium SP2 on a Dell XPS M1730 with built in microphones(2). I inadvertantly disabled the Sigmatel Hi Def Audio microphones from: CONTROL-PANEL\SOUND\RECORDING\CONFIGURE I expected the microphone array would appear with a CONFIGURE option to enable it...but it no longer appears under RECORDING and I can't figure out how to get it back. Any suggestions? Thanks. -Gary ...

Can I make a row "float"?
I would like to make a row float. So if I scroll down to row 211 or whatever, I will have one row tha will always be on top no matter how far down I scroll. Thanks in advance. dvcrogers@sbcglobal.ne -- dvcroger ----------------------------------------------------------------------- dvcrogers's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3262 View this thread: http://www.excelforum.com/showthread.php?threadid=52413 Say you want row 4 to freeze Select row 4, then go to window and freez -- davesexce --------------------------------------------------------...

Outlook 2002 SP3: Can't open file: *.msg. The file may not exist, you may...
Hi, recently I am unable to open attachments within Outlook that have an extension of .msg. When previewing a message in the preview pane, the attachment MSG icon appears white. When the message is opened, it changes to a beige color. Double-clicking (and choosing Open from the menu) on the icon in either state yields no results. When I save the message to my desktop, I took the DDE command used to open files with the .msg extension ("C:\Program Files\Microsoft Office \Office10\OUTLOOK.EXE" /f "%1"). I changed to the directory where the message is and ran: "C:\Program...

Can not access opportunities
We have recently upgrade our CRM 3.0 installation from sql 2000 to sql 2005. We then upgrade SQL to SP2 and now everything is functioning in CRM except we can not access opportunities through the web client. We have also been experiencing random issues with not be able to connect and the database acting up since the upgrade. ...

How can I insert the degree sign with F after it as a shortcut?
I know how to insert the degree sign but can I put in a degree sign with the F after it in a short keyboard stroke or two? You could make it an automatic text entry of various types (AutoText, AutoCorrect, AutoComplete As You Type, depending on your version of Word), or you could assign a keyboard shortcut to the degree sign in the Insert Symbol panel and then type the F. On Nov 26, 3:42=A0pm, Sharrietta <Sharrie...@discussions.microsoft.com> wrote: > I know how to insert the degree sign but can I put in a degree sign with = the > F after it in a short keyboard stroke...

error message
I'm getting an error message as follows: "nable to display the folder. Microsoft Outlook could not access the specified folder location. Out of memory or system resources. Close some windows or programs and try again" I've run the inbox repair tool and have rebooted my computer. No changes. Any suggestions? Try one of the following suggestions here; http://www.howto-outlook.com/faq/outlookdoesntstart.htm -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -Navigation Pane Tips & Tricks -Create an Office XP CD slipstreamed with Service Pac...

Can't send mail after Win2003 SP1 ?
All- After some testing in our labs, we decided to upgrade all production Exchange 2003 servers to Windows Server 2003 SP1. The upgrade went OK, but we did notice one anomaly: Our users who connect via IMAP can no longer send messages. They get an NDR to the effect of " ... you cannot relay for this address" whenever they try to send to external addresses. However, all Outlook 2003 connected users can work just fine and send messages to anyone they want. We checked and re-checked the settings, but nothing seems to help. Anyone else seen this? Hints? Jake Update: To fur...

How can I import "Packed" mainframe data into Excel?
I've copied a file from an IBM Mainframe computer to my PC and need to do some analysis in Excel. Some of the fields in the file are "packed" - for example, the format of one field is " P(9.2). On the mainframe, that takes up six bytes, so $5.90 is stored as "00000000059F" (the last bit is the sign). This is NOT "hex" but an old convention that breaks each byte (EBCDIC 8-bit) into two, 4-bit parts and stores one decimal value in each part. Suggestions about how I can get this format into Excel without having to re-format the data in a mainframe ...

Can't remove Radio Button
I have a sheet which used "Yes/No" Radio Buttons which I've now replaced with Tick Boxes. There is one button which I cannot remove. I can't select it, can't make it active, therefor can't remove it. Please help! Activesheet.Optionbuttons.Delete for each ole in Activesheet.OleObjects if typeof ole.Object is msforms.Optionbutton then ole.delete end if Next -- Regards, Tom Ogilvy "Ken G." <KenG@discussions.microsoft.com> wrote in message news:97DBE155-9089-40BD-B98F-62E372F9CE12@microsoft.com... > I have a sheet which used "...

Can't open pdf files in windows mail.
Every time I try to open a PDF file in windows mail it says I don't have a program associated with it to open it. It says to set a default program with it. I have adobe reader associated with it in default programs, so I don't know why it won't open it. What operating system? i.e.: XP/SP3 etc. Windows Mail, or Windows Live Mail? -- Bruce Hagen MS-MVP Outlook Express Imperial Beach, CA "Dutchy" <Dutchy@discussions.microsoft.com> wrote in message news:5BC3362D-015D-498A-90AD-FE4AD10D0962@microsoft.com... > Every ti...

Can I change my profile name without causing any damage to my files
Hello: Can I change my profile name without causing any damage to my files. Thank you Sure, just go to control panel and create a new profile, name it anything that you want. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the SWEN virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, Douse asked: | Hello: | Can I change my profile name without causing any damage | to my files. | | Thank you ...

Year 1753 in canned KPI Web Parts
I'm playing with the sample company (TWO/Fabrikam), and when I add web parts to the executive portal page (KPI Gross Profit margin for example), I get no data in them. But I do note it is running the query with a date of 1/1/1753 - even though when I set it up I specified Current FY. or Current Period. Any idea on whre it is pulling the year 1753 from? Thanks in advance, ...

How can I view Audit logs in MS-CRM.
Hi, We need to be able to audit changes made to opportunities, quotes, orders etc. by users. Is there a way of viewing the history of changes made to to opportunities, quotes, orders etc. - MS - CRM v 1.20. Sorry but there is no audit facility for Microsoft CRM 1.2 such as a trail showing every transaction. I have requested it as a new feature however even if it does not turn up in the next release it may be possible to write such a tool. However as said for now it does not exist. One option is to look at post callouts as these could be used to write a basic audit log. -- John O'Donn...

Adjust CRM so it can be used for small companies to make invoices
It would be great of crm can be used to make invoices with automatic tax calculation. I'd like the idea of a All in one solution for small in medium sized companies. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Communi...

How can I change the font of a CEdit?
Hi everyonee, I have a CEdit control in my dialog which I want to change its font to "Courier New" with a height of 9. I have been trying without success. Here's how I'm trying: In my OnInitDialog(): LOGFONT lf; CFont font; m_edtCtrl.GetFont()->GetLogFont(&lf); lf.lfWeight = 9; lf.lfFaceName = "Courier New"; font.CreateFontIndirect(&lf); m_edtCtrl.SetFont(&font); Please tell me what is that I'm doing wrong? By the way what other font name than "Courier New" has a fixed width characters? Thank you, Geo "Geo" &...

Can't paste text into a Merged Cell
I've just upgraded to Office 2003 and now I can't paste text selected from Word into a merged cell in Excel - is this normal or am I doing something wrong? Thanks Mark xl2002 works this way, too. (I don't recall the behavior of earlier versions.) Any chance you could just paste into the formula bar? (you might have to reapply the formatting you want, though.) Mark Durrenberger wrote: > > I've just upgraded to Office 2003 and now I can't paste text selected from > Word into a merged cell in Excel - is this normal or am I doing something > wrong? > Than...

Outlook launches and immediately closes. What can I do?
I just bought a new Dell. While trying to configure Outlook, I did something not quiote right. As a result, every time I launch Outlook, it immediately closes. I cannot get it to stop. Anyone know what this is? Or can I erase/delete a file that would return Outlook to its original pre-setup state? How can I get to the point that I can get to the configuration wizard? Thanks! bnjacobs, you wrote on Mon, 16 Jan 2006 18:23:01 -0800: > I just bought a new Dell. While trying to configure Outlook, I did something > not quiote right. As a result, every time I launch Outlook, it immed...

How can I expand a business card to produce a picture ID badge?
I have created a picture business card template. I want to expand that card to be larger than a business card and use it for a picture ID. Can someone offer advice? What size is larger? Open your business card. Open a new Publisher file, page setup, select the size label you need, copy your business card, paste to the new setup. Adjust your objects. -- Mary Sauer http://msauer.mvps.org/ "Melvin" <Melvin@discussions.microsoft.com> wrote in message news:5DCDE432-CCD1-43E7-8088-7E0F05FF0D8D@microsoft.com... >I have created a picture business card template. I want to...

Help- Can't do full backup
Hi, this weekend i had problems makeing a backup from the Exchange IS. I am using NTBackup on a different maschine (windows xp with Exchange System Manager installed) to backup Exchange 2000 (from SBS) with Symantec Mail Security and GFI Faxmaker (recently upgraded from V9 to V12) Here are the errors i am getting: Info ESE 220 - Starting Backup pub2.stm Error ESE 481 - Systemfailure 87, Error -1003 Information Store (3096) An attempt to read from the file "D:\pub\pub2.stm" at offset 0 (0x0000000000000000) for 65536 (0x00010000) bytes failed with system error 87 (0x00000057): &q...