1

I have the below macro that works perfectly fine for my time zone, however it is used in another time zone as well that is 13 hours ahead of me. Anyway to account for this? or an if/else condition to run a different code depending on the now time?

'This part of the macro will use system time to determine the day and run a different version of the macro depending on the result

If Weekday(Now()) = vbFriday Then

'Delete any row that has a cutoff date of further than 3 day from current time (Friday)
    Last = Cells(Rows.Count, iDateColumn).End(xlUp).Row
    For i = Last To 2 Step -1
        If (Cells(i, iDateColumn).Value) > (Now + 3) Then
        Cells(i, iDateColumn).EntireRow.Delete
        End If
    Next
Else

'Delete any row that has a cutoff date of further than 1 day from current time (Mon-Thurs)
    Last = Cells(Rows.Count, iDateColumn).End(xlUp).Row
    For i = Last To 2 Step -1
        If (Cells(i, iDateColumn).Value) > (Now + 1) Then
        Cells(i, iDateColumn).EntireRow.Delete
    End If
    Next
End If
Ram
  • 3,092
  • 10
  • 40
  • 56
CrypTrick
  • 59
  • 1
  • 6
  • Which time zone do you need to calculate the day of the week relative to? A static timezone (like yours), the time zone where it is used, or relative to any time zone? By relative to any, I mean check the day of week for 12 AM MT or 12 AM CT? – Joshua Dannemann Sep 03 '15 at 20:04
  • One way to do it is to have the use the UTC time and date, but that requires some API coding. – Excel Hero Sep 03 '15 at 20:06
  • @ExcelHero You could use the DateDiff function to calculate the number of seconds since Jan 1st 1970 then in conjunction with the DateAdd function calculate a new date adjusted for timezone. However, to avoid using API functions, there would need to be a table in the workbook to reference for the conversions. – Joshua Dannemann Sep 03 '15 at 20:15
  • @JoshuaDannemann The table would need to be updated often because Daylight Savings Time rules in various countries are often in flux. There is no good solution to this. – Excel Hero Sep 03 '15 at 20:21
  • ok then how can I use this code with the 13h time difference accounted for? like now -13? – CrypTrick Sep 03 '15 at 22:01

0 Answers0