Hello everyone,
I need to correct the times and dates of about 700 pages of data. The times in my dataset are out by 7 hours (should have only been out by 6 hours, but thats another story) and I need to convert them to local time and date.
The Times
I fixed this..They were easy to correct, as I just subtracted the 7 hour difference for the entire column to get the local time.
The Dates
I corrected the times but the dates are still in UTC dates, I need local dates. Shown below are the incorrect dates...Basically when the local time reaches midnight I want the local date to increase by a day and then fill the correct date in the corresponding row/columns for local date. Then once the local time reaches midnight again, I want the next day entered into the local date row/columns.
Incorrect Time UTC | Date (Local) | Time (Local)
12-06-2013 23:49 | 12-06-2013 | 16:49
12-06-2013 23:50 | 12-06-2013 | 16:50
12-06-2013 23:51 | 12-06-2013 | 16:51
12-06-2013 23:52 | 12-06-2013 | 16:52
12-06-2013 23:53 | 12-06-2013 | 16:53
12-06-2013 23:54 | 12-06-2013 | 16:54
12-06-2013 23:55 | 12-06-2013 | 16:55
12-06-2013 23:56 | 12-06-2013 | 16:56
12-06-2013 23:57 | 12-06-2013 | 16:57
12-06-2013 23:58 | 12-06-2013 | 16:58
12-06-2013 23:59 | 12-06-2013 | 16:59
12-07-2013 00:00 | 12-07-2013 | 17:00 <------- The local date should not change here
12-07-2013 00:01 | 12-07-2013 | 17:01
12-07-2013 00:02 | 12-07-2013 | 17:02
12-07-2013 00:03 | 12-07-2013 | 17:03
12-07-2013 00:04 | 12-07-2013 | 17:04
12-07-2013 00:05 | 12-07-2013 | 17:05
12-07-2013 00:06 | 12-07-2013 | 17:06
12-07-2013 00:07 | 12-07-2013 | 17:07
12-07-2013 00:08 | 12-07-2013 | 17:08
12-07-2013 00:09 | 12-07-2013 | 17:09
……………….......…. | …………………. | ………………
12-07-2013 06:55 | 12-07-2013 | 23:55
12-07-2013 06:56 | 12-07-2013 | 23:56
12-07-2013 06:57 | 12-07-2013 | 23:57
12-07-2013 06:58 | 12-07-2013 | 23:58
12-07-2013 06:59 | 12-07-2013 | 23:59
12-07-2013 07:00 | 12-07-2013 | 0:00 <------- The local date should change here
12-07-2013 07:01 | 12-07-2013 | 0:01
12-07-2013 07:02 | 12-07-2013 | 0:02
12-07-2013 07:03 | 12-07-2013 | 0:03
I attempted to create a user input box to enter a start date and time. Then the time would increment by 1 minute and the date would not change until it reached midnight, then continue for all 700 pages of data.
But I failed so far. I have tried loops and if then statements, do whiles...I am getting lost and I cannot seem to get it right. Can someone help me with this?
I have attached a sample sheet with a incorrect tab and a corrected tab just in case someone don't follow what I want or am trying to accomplish.
Lost newb
Bookmarks