converting date to nymber

iamkeen

Board Regular
Joined
May 7, 2005
Messages
55
hi

how would you format a date in a textbox on a user form so that it displays as a number.

ie today is 16/11/2009 and i want this to display as 40072.

any help much appreciated

thanks
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
display as the number on the form itself (after the user typed in the date) or in a spreadsheet?

you can use the worksheet formula n(date) to give it the number. Or you can format the cell.

If you want it on the form, then label1.caption = cInt(textbox1.value)
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,769
Try the CLng function:
Code:
Sub xx()
    Dim d As Date
    d = Date
    Debug.Print d, CLng(d)
End Sub
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
How do you get that number?

November 16, 2009 is 40133

Code:
Private Sub UserForm_Activate()
Dim d As Long
d = Date
UserForm1.TextBox1 = d
End Sub
 

iamkeen

Board Regular
Joined
May 7, 2005
Messages
55
thanks

i just made the number up as i wanted to know how to convert the date to a number
 

Watch MrExcel Video

Forum statistics

Threads
1,130,309
Messages
5,641,448
Members
417,209
Latest member
Agbarker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top