I want to manipulate date entered in excel sheet using VB macro, ie want to
extract day, moth and year separately from the date entered in EXCEL sheet
(in dd-mm-yy format )and store it in three different variables. but since the
formats used in excel is dd-mm-yy and VB is mm/dd/yy could any body please
code a function to do the same Thanks very much

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200908/1

Sub test()
Dim dt As Date
Dim d As Long, m As Long, y As Long

Range("a1") = Now ' or Date

dt = Range("A1").Value

d = Day(dt)
m = Month(dt)
y = Year(dt)

Debug.Print d, m, y

End Sub

press ctrl-g to see the debug results in the Immediate window

Regards,
Peter T


"nanda via OfficeKB.com" <u54234@uwe wrote in message
news:9b2a3a8507635@uwe...
I want to manipulate date entered in excel sheet using VB macro, ie want
to
extract day, moth and year separately from the date entered in EXCEL sheet
(in dd-mm-yy format )and store it in three different variables. but since
the
formats used in excel is dd-mm-yy and VB is mm/dd/yy could any body please
code a function to do the same Thanks very much

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200908/1

if the cell value is a date, then in VBA use the DATE() MONTH() and YEAR()
functions ...they have nothign to do with what format you use

set target = range("A1")
msgbox "Year:" & year(target) & ", Month:" & month(target) & ", Day:" &
day(target)

"nanda via OfficeKB.com" wrote:

I want to manipulate date entered in excel sheet using VB macro, ie want to
extract day, moth and year separately from the date entered in EXCEL sheet
(in dd-mm-yy format )and store it in three different variables. but since the
formats used in excel is dd-mm-yy and VB is mm/dd/yy could any body please
code a function to do the same Thanks very much

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200908/1

On Aug 26, 10:44*am, "nanda via OfficeKB.com" <u54234@uwe wrote:
I want *to manipulate date entered in excel sheet using VB *macro, ie want to
extract day, moth and year separately from the date entered in EXCEL sheet
(in dd-mm-yy format )and store it in three different variables. but since the
formats used in excel is dd-mm-yy and VB is mm/dd/yy could any body please
code a *function to do the same Thanks very much

--
Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-programming/200908/1

You can try with GemBox.Spreadsheet. It has methods to format date in
cells.
http://www.gemboxsoftware.com/GBSpreadsheet.htm

Thank you very much for the immediate reply.


But Im sorry to say that I coded my query in a wrong way. Actually I wanted
to know comparison of two dates of excel in macro, since there is mismatch
of formats (EXCEL :DD-MM-YY & VB:mm/dd/yy)

Could you help me coding - how to compare two dates in Macro.
Suppose A1 contains 28-11-00. I want to check whether the value in A1 is
LESS THAN 02-3-01 and greater than or equal to 16-12-98 .

Thanking you in anticipation

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200908/1

I wanted to know comparison of two dates of excel in macro, since there is
mismatch of formats (EXCEL :DD-MM-YY & VB:mm/dd/yy)

Could you please help me coding - how to compare two dates in Macro since i
am not an expert in macro
Suppose A1 contains 28-11-00. I want to check whether the value in A1 is
LESS THAN 02-3-01 and greater than or equal to 16-12-98 .

Thanking you in anticipation

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200908/1

thanks for the immediate response
But Im sorry to say that I coded my query in a wrong way. Actually I wanted
to know comparison of two dates of excel in macro, since there is mismatch
of formats (EXCEL :DD-MM-YY & VB:mm/dd/yy)

Could you help me coding - how to compare two dates in Macro.
Suppose A1 contains 28-11-00. I want to check whether the value in A1 is
LESS THAN 02-3-01 and greater than or equal to 16-12-98 .

--
Message posted via http://www.officekb.com

thanks for the immediate response
But Im sorry to say that I coded my query in a wrong way. Actually I wanted
to know comparison of two dates of excel in macro, since there is mismatch
of formats (EXCEL :DD-MM-YY & VB:mm/dd/yy)

Could you help me coding - how to compare two dates in Macro.
Suppose A1 contains 28-11-00. I want to check whether the value in A1 is
LESS THAN 02-3-01 and greater than or equal to 16-12-98 .

--
Message posted via http://www.officekb.com

On Sat, 29 Aug 2009 07:44:34 GMT, "lataa3 via OfficeKB.com" <u54234@uwe wrote:

Thank you very much for the immediate reply.


But I’m sorry to say that I coded my query in a wrong way. Actually I wanted
to know comparison of two dates of excel in macro, since there is mismatch
of formats (EXCEL :DD-MM-YY & VB:mm/dd/yy)

Could you help me coding - how to compare two dates in Macro.
Suppose A1 contains 28-11-00. I want to check whether the value in A1 is
LESS THAN 02-3-01 and greater than or equal to 16-12-98 .

Thanking you in anticipation

Excel stores dates as serial numbers beginning with 1 (= 1 Jan 1900)

The format only determines how that serial number is displayed. It is
completely irrelevant for what you want to do (assuming it has been entered as
an Excel date, and is not a text representation of the date).

Here is one way to do what you describe:

==========================
Option Explicit
Sub CompDate()
Dim d1 As Date, d2 As Date, d3 As Date
Dim bRes As Boolean

'Set up parameters for test
With Range("a1")
.NumberFormat = "dd-mm-yy"
.Value = DateSerial(2000, 11, 28)
End With

d2 = DateSerial(2001, 3, 2)
d3 = DateSerial(1998, 12, 16)

'Do the test
d1 = Range("A1").Value

If d1 < d2 And d1 = d3 Then
bRes = True
Else
bRes = False
End If

Debug.Print "Date comparison is " & bRes

End Sub
=================================

The above will return a True.
--ron