Date to number format using macro

moses - Aug 20, 2008 at 08:52 AM
 Bluetubs - Sep 7, 2010 at 11:23 AM
in a macro I have captured the date into a variable. I need to convert that date to a number format to used for further processing.

2 responses

See below for a VBA function that converts 3/15/97 into the date number 35504. Of course it can be adapted for any date you have.

Function DateNumber()
Dim MyDate As Date
MyDate = #3/15/1997#
DateNumber = MyDate - #1/1/1900# + 2
MsgBox DateNumber 'Will show 35504
End Function
'for date to display as text

cell.NumberFormat = "@" 'Text

'to change date style

cell.NumberFormat = "dd/mm/yyyy" 'or whatever style
cell.NumberFormat = "dd/mm/yyyy"

where to write this excel sheet?