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

 

[Livin404]

My second question was not about what format the sheet is in, it was what format you want the new result in?



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.

Actually everything is in General format and it can remain in a General format unless that simply will not work. I tried running the formula and I changed the Fomat in Column D and F to Text, Date and I'm still getting a #VALUE! in Column F. It's reading something, and I'm getting to run the macro without getting a message code. I"m so sorry about this as I know you have other things to do. I'll continue to work on it too. Incidentally we have the code sending the results to Column F, I take it I can just change to D? I will have to delete Column D and move F over to where D is if I need to. What I hope to happen once I get this sorted out is to have a header for the columns. There will be 3 or 4 (blocks) of flights of up to 72 hours going down the rows. Each block will have header on it, I already have it coded to insert a blank line between the days. Column A will have the date, so once the times are updated by subtracting the 7 hours, then Column D will only have the time. I provided an image of what I ultimately want to achieve. This is why I'm trying to adjust the times accurately so the flights can be properly grouped.

 

[Peter_SSs]

Actually everything is in General format and it can remain in a General format

i don't want to know what the cell format is. I want to know if you want the date time that ends up in the cell to be numeric or text or you don't care?

I'm still getting a #VALUE!

Therefore I still want ..

... the sample data with XL2BB so that I can test with your data rather mine

 

[Livin404]

i don't want to know what the cell format is. I want to know if you want the date time that ends up in the cell to be numeric or text or you don't care?


Therefore I still want ..

Thank you, I’ll have to work on it from home work administrators are very protective of anything dealing with computers. I’ll get back to you. Thank You

 

[Livin404]

Thank you, I'm still working on that. I just was working the last few days on different elements of the same project. That is one of 2 or 3 things I have left. You must have seen the one I just posted, then the last item left would be deleting the date in Column D only leaving the time. I will it once it is complete. Thank you for your continued support.

 

[Livin404]

My second question was not about what format the sheet is in, it was what format you want the new result in?



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.

Hello, I finally may have some good news regarding your formula. Apologies about the delay there were other elements of this project I had been working on, and I felt a little intimidated using the XL2BB; actually I was about ready to look into that process.
I tried one more time and this time I changed Column F from a General Format to a Custom Format, and with your VBA it changed it perfectly. I did notice that it only changed the first one. Is there a way it can be tweaked so that all changes are done in Column D and it has the appearance it currently has (no colons, # Values, etc.? Thank you,

 

[Livin404]

I found out why I was getting # Value!. For some reason when this thing is pasted it inserts an extra space. I'll have to figure out with the use of Trim to get rid of the space.

 

[Livin404]

i don't want to know what the cell format is. I want to know if you want the date time that ends up in the cell to be numeric or text or you don't care?


Therefore I still want ..

Great news it's done. I had to trim/substitute clean etc. Apparently it's an issue copying data from the internet. Thank you for your patience. I'll see if I can close this.

 

[Peter_SSs]

Glad you got it sorted.