Date dd-mm-yy(EXCEL) manipulation in VB macro

[nanda via OfficeKB.com]

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

 

[Peter T]

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

 

[Patrick Molloy]

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)

 

[filip]

I want  to manipulate date entered in excel sheet using VB  macro, iewant 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 sincethe
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

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

 

[lataa3 via OfficeKB.com]

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 D-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

 

[lataa3 via OfficeKB.com]

I wanted to know comparison of two dates of excel in macro, since there is
mismatch of formats (EXCEL D-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

 

[lataa3 via OfficeKB.com]

thanks for the immediate response
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 D-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 .

 

[lataa3 via OfficeKB.com]

thanks for the immediate response
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 D-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 .

 

[Ron Rosenfeld]

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 D-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