Excel change dates in column by minus 7 hours

I have a report coming in that the time is off by 7 hours.  I need to change the time to -7 hours for the whole column.  Any ideas on how to do this.
ZantisAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Tj aCommented:
Assuming that the times you want to change are in column A, you could insert "7:00:00" in an empty cell e.g cell D4
In a new column use the formula "=SUM(A1 + $D$4)" This should change the time to 7 hours ahead. drag down the formula.
ProfessorJimJamMicrosoft Excel ExpertCommented:
assuming your data is in column A starting from second row

put this formula  =A2-TIME(7,0,0)


copy the formula down

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ZantisAuthor Commented:
Date and time look like this 7/7/2015 2:24:38 AM
 
 and need to be -7 hours not +7
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

ZantisAuthor Commented:
Professor JimJam, your solution almost works but it takes away the date.
ProfessorJimJamMicrosoft Excel ExpertCommented:
what do you mean takes away date?   you mean to reformat the column where you have put the formula
ProfessorJimJamMicrosoft Excel ExpertCommented:
please see attached.

seven before this date and time 7/7/2015 2:24   becomes previous date 7/6/2015 19:24

and if 7/6/2015 19:24 result is incorrect then please tell me what should be the correct result for date 7/7/2015 2:24 conversion of -7
Book1.xlsx
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Assuming your date and time is in A1, then try this......

=INT(A1)+MOD(A1-INT(A1)-TIMEVALUE("7:0:0"),1)

Is this what you are trying to achieve?
ZantisAuthor Commented:
Date and time currently look like this 7/7/2015 2:24:38 AM
 
 and need to be -7 hours not +7

so it should look like this... 7/6/2015 7:24:38 PM

and when you use this =A2-TIME(7,0,0) it removed the date and the time is the only thing left.

I still want the date included.
ZantisAuthor Commented:
Nevermind, I just needed to format the date correctly using a custom date

m/d/yyyy h:mm:ss AM/PM
ZantisAuthor Commented:
I just needed to do a custom format to recreate the date/time to look the same using this...

m/d/yyyy h:mm:ss AM/PM
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
It seems to be a formatting issue. Maybe your formula cells are formatted as Time. For example copy the formatting of cell A2 with format painter and apply it to the formula cell.

Have you looked at the file attached by ProfessorJimJam? It is giving you what you are trying to achieve.

Does this help?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.