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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Date and time look like this 7/7/2015 2:24:38 AM
and need to be -7 hours not +7
and need to be -7 hours not +7
ASKER
Professor JimJam, your solution almost works but it takes away the date.
what do you mean takes away date? you mean to reformat the column where you have put the formula
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
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
Assuming your date and time is in A1, then try this......
=INT(A1)+MOD(A1-INT(A1)-TI
Is this what you are trying to achieve?
=INT(A1)+MOD(A1-INT(A1)-TI
Is this what you are trying to achieve?
ASKER
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.
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.
ASKER
Nevermind, I just needed to format the date correctly using a custom date
m/d/yyyy h:mm:ss AM/PM
m/d/yyyy h:mm:ss AM/PM
ASKER
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
m/d/yyyy h:mm:ss AM/PM
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?
Have you looked at the file attached by ProfessorJimJam? It is giving you what you are trying to achieve.
Does this help?
In a new column use the formula "=SUM(A1 + $D$4)" This should change the time to 7 hours ahead. drag down the formula.