Subtract 4 hour from all cells in a column

[RandyD123]

I have a column of times that are formatted 12:30:00, 14:02:00.....so on and so on. I want to convert these times to be 4 hours less than what they are. Is there a formula I can put in a cell somewhere that will do this for me?

[sktneer]

If you have time in cell A2, try this.....

Please Login or Register  to view this content.

[etaf]

put 4 hours into a cell 04:00
then
right click on the that cell and copy
select the column with the hours you want to change
go to paste special
and use the subtract option
that should apply the 4 hour subtraction to all the cells

[RandyD123]

That works but I think that I want all the time cells in the "A" column to look at a cell and then change to the minus 4 hours. So if cell A2 is 12:30:00 it will look at a cell and change to 8:00:00

[etaf]

you will probably need VBA to change the contents of the column itself - so that if you enter a time - then it looks at a cell and changes the time you entered to a different time - VBA would need to do that ,i think
not my area of expertise
if you can use a different cell , then the formula supplied by sktneer with a slight modification

instead of 4 - use the cell reference where you want the time difference to be used

[newdoverman]

What etaf gave you will subtract the 4 hours from your existing data. Otherwise, in column B1 or where ever the data starts enter:

Please Login or Register  to view this content.

where F1 is where you have the 4:00 hours that you want to subtract.

[bebo021999]

So if cell A2 is 12:30:00 it will look at a cell and change to 8:00:00

So you like to round down to hour, after 4 hours subtracted:
=FLOOR(A1-4/24,1/24)

[sktneer]

You may try this code to see if this helps.......
Assuming you have time values in col. A and start from cell A2.
And in cell B2 you have input 4 which you want to subtract from the time values in col. A. If this is not B2 in your sheet, you may change this in the code as per requirement. To do so just change B2 in red color.

Please Login or Register  to view this content.

[RandyD123]

I should just post what it is I am trying to do!!!! I really have a file that I want to convert (insert a PIVOT Table) by running a VB script. I will post the "DATA File" and then what the final result should be. I can do it manually but I think I need 2 files. The fist one would be saved to my destop as "DATA.XLSX" and I would open a second file that has the "code" to run against the data file and produce a PIVOT table.

In the file I uploaded all my times are plused 4 hours. I need to minus 4 hours from each one AND it need to change the date if after the minuse 4 hours changes the date column!!!!

The DATA file will change everyday.

[newdoverman]

I don't know what you are wanting VBA for but here is the Pivot Table with -4 hours shown. This is in TABULAR format and not compact form...you can choose what you want.