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?
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.
0
ProfessorJimJamCommented:
assuming your data is in column A starting from second row

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


copy the formula down
0

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
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

ZantisAuthor Commented:
Professor JimJam, your solution almost works but it takes away the date.
0
ProfessorJimJamCommented:
what do you mean takes away date?   you mean to reformat the column where you have put the formula
0
ProfessorJimJamCommented:
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
0
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?
0
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.
0
ZantisAuthor Commented:
Nevermind, I just needed to format the date correctly using a custom date

m/d/yyyy h:mm:ss AM/PM
0
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
0
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?
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.