Macro - for a range of time set date to previous day

derek_e2015

New Member
Joined
May 18, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
Looking to see if there is a way using a macro to change the date in a column to the previous day. there is a column in this a worksheet that is formatted as follows: 4/5/2019 2:40 AM
If the time is between 12:00 AM and 3:00 AM I need the date to be changed to the previous day. So for the example above, it needs to be
4/4/2019 2:40 AM

I have a list of items that I really don't want to have to search through to find the times between 12am and 3am and manually change the date. was hoping there was a way to write up a macro to do this
thank you
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,334
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

Try this:
VBA Code:
Sub MyMacro()

    Dim c As String
    Dim lr As Long
    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Specify which column to run against
    c = "A"
    
'   Find last row in column with data
    lr = Cells(Rows.Count, c).End(xlUp).Row
    
'   Loop through all rows starting in row 2
    For r = 2 To lr
        If Cells(r, c) - Int(Cells(r, c)) <= (1 / 8) Then
            Cells(r, c) = Cells(r, c) - 1
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub
Since you did not provide any information on the ranges to apply this to, you may need to amend your code accordingly.
But I documented it all so it should be obvious where you may need to make changes.
 
Solution

derek_e2015

New Member
Joined
May 18, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
Welcome to the Board!

Try this:
VBA Code:
Sub MyMacro()

    Dim c As String
    Dim lr As Long
    Dim r As Long
   
    Application.ScreenUpdating = False
   
'   Specify which column to run against
    c = "A"
   
'   Find last row in column with data
    lr = Cells(Rows.Count, c).End(xlUp).Row
   
'   Loop through all rows starting in row 2
    For r = 2 To lr
        If Cells(r, c) - Int(Cells(r, c)) <= (1 / 8) Then
            Cells(r, c) = Cells(r, c) - 1
        End If
    Next r
   
    Application.ScreenUpdating = True
   
End Sub
Since you did not provide any information on the ranges to apply this to, you may need to amend your code accordingly.
But I documented it all so it should be obvious where you may need to make changes.
Thank you Joe4 That worked Perfect
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,334
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Glad it works for you!
:)
 

derek_e2015

New Member
Joined
May 18, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

You are welcome.
Glad it works for you!
:)
Hello Joe4, Thanknyou again for the code. Would it be possible for you to explain this Line: Cells(r, c) - Int(Cells(r, c)) <= (1 / 8)

Trying to figure out how that relates to the 12am - 3am Time range.
Thank you
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,334
Office Version
  1. 365
Platform
  1. Windows
The key is understanding how Excel stores dates and times. They are really stored as large numbers.
To see this, go to any valid date/time entry in Excel, and temporarily change the cell format to "General". This is how Excel actually stores and sees the dates/times.
Making it a date is just applying a special format to the cell (and formatting does not change the value, just the visual representation).

The integer portion of the number is the number of days since 1/0/1900.
And time is just the fractional component of one day.
So 3:00 AM would just be 3/24 (as there are 24 hours in one day), which simplifies to 1/8.

In Excel, the INT function takes the integer value of a number (meaning it drops the decimals). So if we want to get just the time piece by itself, we can take our original number, and then subtract the integer portion of that number, leaving just the decimal piece (which is the time piece).

I hope that clarifies things. Let me know if you have any other questions.
 

derek_e2015

New Member
Joined
May 18, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

The key is understanding how Excel stores dates and times. They are really stored as large numbers.
To see this, go to any valid date/time entry in Excel, and temporarily change the cell format to "General". This is how Excel actually stores and sees the dates/times.
Making it a date is just applying a special format to the cell (and formatting does not change the value, just the visual representation).

The integer portion of the number is the number of days since 1/0/1900.
And time is just the fractional component of one day.
So 3:00 AM would just be 3/24 (as there are 24 hours in one day), which simplifies to 1/8.

In Excel, the INT function takes the integer value of a number (meaning it drops the decimals). So if we want to get just the time piece by itself, we can take our original number, and then subtract the integer portion of that number, leaving just the decimal piece (which is the time piece).

I hope that clarifies things. Let me know if you have any other questions.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,334
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

Forum statistics

Threads
1,136,734
Messages
5,677,447
Members
419,693
Latest member
divtjd

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
Top