Adjust Large Number of Cells for Daylight Saving Time

[twills]

Hello,

I have been scratching my head a bit for on this one and checked the forum, but can't seem to come up with a solution at the moment.

Currently I have a large quantity of data in cells spread over various worksheets that are currently daylight saving time adjusted and I'd like to change that so that they aren't.

I'd like to have a macro that asks me what the daylight saving times and dates are, and if the cell contains a date that falls within this period, to subtract one hour from the time.

The data in the workbook starts in worksheet 2 onwards (sheet one is like a coversheet), also the number of worksheets within the workbook is variable, as is the number of rows containing data within each workbook. Incidentally, the data in the workbooks always starts in cell A5.

I have included a sample of one of the workbooks and would be most grateful for your thoughts and / or suggestions.

Many Thanks in advance,
twills

[davegugg]

Are the dates that you are wanting to change always going to be only in column A?

So basically:

For sheets 2 through last sheet
for column A rows 5 to last row
if date is in daylight savings, subtract 1 hour

Is that a correct summary?

[twills]

Dave,

Yes, that's exactly spot on.

twills

[davegugg]

How's this work for you?

Public Sub twills()

Dim lngLastRow As Long
Dim StartDaylight As Date
Dim EndDaylight As Date

StartDaylight = CDate(InputBox("Enter the date of daylight savings start.", "Daylight Savings Start Date"))
EndDaylight = CDate(InputBox("Enter the date of daylight savings end.", "Daylight Savings End Date"))

For i = 2 To Sheets.Count
    lngLastRow = Sheets(i).Cells(Rows.Count, 1).End(xlUp).Row
    For j = 5 To lngLastRow
        If Sheets(i).Cells(j, 1).Value >= StartDaylight Then
            If Sheets(i).Cells(j, 1).Value <= EndDaylight Then
                Sheets(i).Cells(j, 1).Value = Sheets(i).Cells(j, 1).Value - 1 / 24
            End If
        End If
    Next j
Next i

End Sub