Subtract 7 hours from time using the mm.dd.yyyy hh:mm

Livin404

Well-known Member
Joined
Jan 7, 2019
Messages
743
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Good afternoon,
I've spent the better part of 6 hours trying to figure out how to subtract 7 hours from a column that contains the date and time in a "mm.dd.yyyy hh:mm" format. I've included an image. After this I will drop off the date leaving me just a 24 hour time. I will eventually like to have the the date to include the day THURSDAY NOVEMBER 15 be part of a header after I manage to code the schedule into 24 hour blocks separated by two rows.
For now I just want to subtract the 7 hours from each cell in Column D. Moreover, is there anything I need to do account if there is a day change because the loss of time, for example the first date and time is 15 Oct 2020 0245.
My most current effort has yielded nothing except errors. So far I have 8 codes just to get the table you see on the image.
Thank you,
VBA Code:
Sub Roll_Call()
Dim Roll_Call As Date
Roll_Call = "mm.dd.yyyy hh:mm"
Range("D1") = DateAdd("mm.dd.yyyy hh:mm", -7, Roll_Call)
End Sub
 

Attachments

  • Mr. Excel.JPG
    Mr. Excel.JPG
    60.8 KB · Views: 37

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
hi
In a worksheet function you can do
Excel Formula:
=a1-time(7,0,0)

within the vba. -->
VBA Code:
range("D1") = Roll_Call - 7/24
dates are stored as numbers with the time being stored as a decimal portion
 
Upvote 0
hi
In a worksheet function you can do
Excel Formula:
=a1-time(7,0,0)

within the vba. -->
VBA Code:
range("D1") = Roll_Call - 7/24
dates are stored as numbers with the time being stored as a decimal portion
Thank you I’ll give a go very soon. One thing more, you will notice in the image I posted the change in time also affects the date, is there an If state to cover that.
Thank you.
 
Upvote 0
You can extract the time portion of your date and multiply it by 24 to get hours and if it's more than 7 the date will not change
 
Upvote 0
You can extract the time portion of your date and multiply it by 24 to get hours and if it's more than 7 the date will not change
Thanks for getting back to me I tried the formula, I added a the sub and dim and even tried the VBA formula you suggested, and I'm still making a mistake some place. Any more suggestions?
VBA Code:
Sub Roll_Call()
Dim Roll_Call As Date
Roll_Call = "mm.dd.yyyy hh:mm"
Range("D1") = Roll_Call - 7 / 24
End Sub
 
Upvote 0
Assuming that the dates are text as in this other thread, then for a worksheet formula you could try the one shown in column E to produce real (numeric) time/date values, or this vba to produce the same numeric results in column F.

VBA Code:
Sub Subtract7Hours()
  With Range("F1:F" & Range("D" & Rows.Count).End(xlUp).Row)
    .NumberFormat = "dd mmm yyyy hh:mm"
    .Formula = "=LEFT(D1,11)+TIMEVALUE(REPLACE(RIGHT(D1,4),3,0,"":""))-7/24"
    .Value = .Value
  End With
End Sub

Livin404.xlsm
DEF
115 Oct 2020 024514 Oct 2020 19:4514 Oct 2020 19:45
215 Oct 2020 114015 Oct 2020 04:4015 Oct 2020 04:40
316 Oct 2020 142416 Oct 2020 07:2416 Oct 2020 07:24
418 Sep 2020 011117 Sep 2020 18:1117 Sep 2020 18:11
51 Jan 2020 120001 Jan 2020 05:0001 Jan 2020 05:00
Sheet1
Cell Formulas
RangeFormula
E1:E5E1=LEFT(D1,11)+TIMEVALUE(REPLACE(RIGHT(D1,4),3,0,":"))-7/24
 
Upvote 0
Solution
Hello, thanks for the input, I tried the VBA you mentioned and I ended up getting #VALUE! in Column F in both the General and Text format. I take it once Column F is properly filled I would need another formula to Delete Column D, or just cut and past over Column D with Column F?
 
Upvote 0
You asked about your second question regarding the number or text format. The worksheet is actually in a General format, and going back to your first question in the same post I would definitely want the change in date and/or time in the same Column otherwise I would need another VBA just to delete the source column.
Thank you,
 
Upvote 0
Thanks for getting back to me I tried the formula, I added a the sub and dim and even tried the VBA formula you suggested, and I'm still making a mistake some place. Any more suggestions?
VBA Code:
Sub Roll_Call()
Dim Roll_Call As Date
Roll_Call = "mm.dd.yyyy hh:mm"
Range("D1") = Roll_Call - 7 / 24
End Sub
Hi
yes you've defined Roll_Call as your date format rather than the actual date.

VBA Code:
Function Roll_Call(inDate As Date ) AS Date
Roll_Call = inDate - 7 / 24
End Function
in excel if you want to have e2 be the date based on d1 add the following formula
Excel Formula:
=Roll_Call(d1)
Set the date format in the field itself rather than as part of the calculation.
 
Upvote 0
You asked about your second question regarding the number or text format. The worksheet is actually in a General format ..
My second question was not about what format the sheet is in, it was what format you want the new result in?
Do you want the new date/times to be text values like the original ones or do you want them to be 'real' date/time values (numeric)?


If you are getting errors with my code then perhaps you could provide the sample data with XL2BB so that I can test with your data rather mine, since you can see that the formula and code did not result in errors with my data. :)


BTW, having a procedure name the same as one of the variable names (Roll_Call) in that procedure is not a good idea.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

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