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

Livin404

Active Member
Joined
Jan 7, 2019
Messages
369
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: 18

Livin404

Active Member
Joined
Jan 7, 2019
Messages
369
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.
 

Attachments

  • Header details.JPG
    Header details.JPG
    20 KB · Views: 3

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows

Livin404

Active Member
Joined
Jan 7, 2019
Messages
369
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

Active Member
Joined
Jan 7, 2019
Messages
369
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

Active Member
Joined
Jan 7, 2019
Messages
369
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

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,
 

Attachments

  • UPDATE 72 HOUR.JPG
    UPDATE 72 HOUR.JPG
    64.2 KB · Views: 1

Livin404

Active Member
Joined
Jan 7, 2019
Messages
369
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

Active Member
Joined
Jan 7, 2019
Messages
369
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,253
Messages
5,600,544
Members
414,387
Latest member
Vincent88

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