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

[Livin404]

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,

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

 

[rondeondo]

hi
In a worksheet function you can do

=a1-time(7,0,0)

within the vba. -->

range("D1") = Roll_Call - 7/24

dates are stored as numbers with the time being stored as a decimal portion

 

[Livin404]

hi
In a worksheet function you can do

=a1-time(7,0,0)

within the vba. -->

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.

 

[rondeondo]

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

 

[Livin404]

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?

Sub Roll_Call()
Dim Roll_Call As Date
Roll_Call = "mm.dd.yyyy hh:mm"
Range("D1") = Roll_Call - 7 / 24
End Sub

 

[Peter_SSs]

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.

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
	D	E	F
1	15 Oct 2020 0245	14 Oct 2020 19:45	14 Oct 2020 19:45
2	15 Oct 2020 1140	15 Oct 2020 04:40	15 Oct 2020 04:40
3	16 Oct 2020 1424	16 Oct 2020 07:24	16 Oct 2020 07:24
4	18 Sep 2020 0111	17 Sep 2020 18:11	17 Sep 2020 18:11
5	1 Jan 2020 1200	01 Jan 2020 05:00	01 Jan 2020 05:00
Sheet1

Cell Formulas
Range	Formula
E1:E5	E1	=LEFT(D1,11)+TIMEVALUE(REPLACE(RIGHT(D1,4),3,0,":"))-7/24

 

[Livin404]

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?

 

[Livin404]

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,

 

[rondeondo]

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?

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.

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

=Roll_Call(d1)

Set the date format in the field itself rather than as part of the calculation.

 

[Peter_SSs]

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.