Date problem

G

greenparrot

New Member
Joined
Jan 30, 2004
Messages
41
Morning

I have a macro, which sorts data, add values and creates dates and week no.s, all works fine apart from the date part, during the marco the date gets input into a cell as 04-Oct for example, i want it the other way around and it also does this but it when it changes the date formatting, it turns 04 to 05.

anyone have any ideas?

For x = 1 To rowscount

If ActiveCell.Offset(0, 8).Value >= 0 Then
ActiveCell.Offset(0, 6).Value = "UP"
End If

If ActiveCell.Offset(0, 8).Value < 0 Then
ActiveCell.Offset(0, 6).Value = "DOWN"
End If

If ActiveCell.Offset(0, 12).Value = "UW" Then
ActiveCell.Offset(0, 3).Value = "Waste"
End If

If ActiveCell.Offset(0, 12).Value = "UI" Or ActiveCell.Offset(0, 12).Value = "UA" Or ActiveCell.Offset(0, 12).Value = "PI" Or ActiveCell.Offset(0, 12).Value = "PH" Then
ActiveCell.Offset(0, 3).Value = "Adjustment"
End If

If ActiveCell.Offset(0, 12).Value = "UE" Then
ActiveCell.Offset(0, 3).Value = "Staff"
End If

If ActiveCell.Offset(0, 12).Value = "UC" Then
ActiveCell.Offset(0, 3).Value = "S/C"
End If

If ActiveCell.Offset(0, 9).Value <= 0 Then
ActiveCell.Offset(0, 20).Value = ActiveCell.Offset(0, 9).Value * -1
Else
ActiveCell.Offset(0, 20).Value = ActiveCell.Offset(0, 9).Value * 1
End If


startdate = #9/19/2004#
mydate = ActiveCell.Offset(0, 10).Value
weekno = DateDiff("ww", startdate, mydate)
thisweekno = DateDiff("ww", startdate, Date)
myformatmonth = Format(mydate, "mmm-yy")

myyear = DatePart("yyyy", mydate)
ActiveCell.Offset(0, 5).Value = weekno
ActiveCell.Offset(0, 4).Value = myformatmonth
ActiveCell.Offset(1, 0).Select

Next x
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
When using dates there is a need to to be aware of when you are dealing with a serial number or a string. what you have is not apparent from you code. The situation is confused by Excel "helpfully" converting dates into strings or numbers wherever it seems appropriate. "It is a feature, not a bug."

When using a date in worksheet cells it is best to use the serial number and format the cell as required. Serial numbers can be used in calculations.

When typing a date into a worksheet cell it is automatically converted to a serial number with the format used when typing but interpreting the day/month/year according to the global windows settings.

The date format you are using in the line
startdate = #9/19/2004#
is correct for VBA. Unless we force it to do otherwise, VBA will automatically interpret such a date as as being in the month/day/year format (ignoring global settings) and convert it to a date serial number accordingly. In the UK we cannot use the #date# VBA code because we need day-month-year.

It is often useful to define different variable types for dates. Not only does this force the conversion to the data type, but it helps keep track of what exactly is contained in the variable.

All sorts of scenarios are possible, but hopefully this gives some ideas for experiment :
Code: 
Sub test()
    Dim UKwrong As Date
    Dim MyDateString As String
    Dim MyDateSerial As Double  ' use this to force correct conversion
    Dim MyNewDate As Date       ' stored as a serial number
    Dim MyNewDateString As String
    '------------------------------------------------------------------
    '- cannot use #1/12/2005# in UK (XL97)
    UKwrong = #1/12/05#
    '- make a string (could be interpreted mm/dd/yy)
    MyDateString = "1/12/2005" ' 1st.of December
    '-----------------------------------------------------------------
    '- convert to a serial number
    '- this converts correctly in UK. so uses global Windows settings.
    MyDateSerial = _
        DateSerial(Year(MyDateString), Month(MyDateString), Day(MyDateString))
    '-----------------------------------------------------------------
    '- check conversion back to a Date object
    MyNewDate = MyDateSerial    ' defined as Date forces conversion
    MyNewDateString = Format(MyNewDate, "dd mmmm yyyy")
    ActiveSheet.Range("A1").Value = MyDateSerial
    '-----------------------------------------------------------------
    '- msgbox automatically converts Date variable (serial) to a string.
    MsgBox ("Serial Number : " & MyDateSerial & vbCr _
            & "Date Variable :  " & MyNewDate & vbCr _
            & "String Variable : " & MyNewDateString & vbCr _
            & "UK wrong        : " & UKwrong)
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

Y
  • Solved
AutoFilter with Dates - Date range, yes; single date, no
Replies
7
Views
278
Alex Blakenburg
A
D
  • Question
VBA code works when called directly, but not from another routhine
Replies
7
Views
899
davidepstein22
D
A
  • Question
Get enddatetime if X no. of working hours is added to startdatetime
Replies
1
Views
363
myall_blues
myall_blues
C
  • Question
Adding a hyperlink to an automated email
Replies
1
Views
576
Micron
M
C
  • Question
Month to date totals by category
Replies
0
Views
538
chazrab
C

Forum statistics

Threads
1,220,965
Messages
6,157,120
Members
451,399
Latest member
alchavar

Share this page

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
Back
Top