Date to number format using macro [Closed]

 Bluetubs -
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 replies

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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

'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?