+ Reply to Thread
Results 1 to 6 of 6

Changing time offset in Excel column

  1. #1
    Registered User
    Join Date
    03-06-2015
    Location
    Slovenia
    MS-Off Ver
    Office 2010
    Posts
    3

    Changing time offset in Excel column

    Friends, as my last resort, I came to this forum, to ask experts on how to solve a problem. Namely:
    I have an excel file with three columns. Column A is timecode. Columns B and C are texts. There are over 700 rows in this file (I have to do this for approximately 30 different files). What I would like to do is to change time offset in the first column by adding 2 minutes 35 seconds to all rows in column A. For example:

    01:12:10:21 IZZY (voiceover) Pixie Dust Away!

    01:12:15:03 IZZY Jake! Cubby! Wake up!

    01:12:18:04 JAKE What's going on, Izzy?

    would have to be:

    01:14:45:21 IZZY (voiceover) Pixie Dust Away!

    01:14:50:03 IZZY Jake! Cubby! Wake up!

    01:14:35:04 JAKE What's going on, Izzy?

    etc.

    The first problem is that the time format 01:12:10:21 means hours:minutes:seconds:frames, however, frames are not important and can be omitted, only time units are important. I really have no idea how to approach this problem. I would truly appreciate your help.
    Kind regards and thank you very much. I'm attaching the file if anyone is willing to help... Thank you kindly.

    jake1.xls
    Last edited by FlyerSG; 03-06-2015 at 11:41 AM.

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,468

    Re: Changing time offset in Excel column

    I wrote a little script that should do it for you, and will preserve the frames.

    To use VBA code go to Excel, hit Alt+F11, Click Insert->Module, and paste the code there, and close that window.

    To call the code, hit Alt+F8 and choose to run ChangeTime.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Registered User
    Join Date
    03-06-2015
    Location
    Slovenia
    MS-Off Ver
    Office 2010
    Posts
    3

    Re: Changing time offset in Excel column

    Wooow, daffodil11, this is absolutely awsome. It works perfectly. I wish I could somehow help you with something in return... This was really a great favour. Perhaps just a hint, how to change the time myself in this code? If, for example I would need 2 minutes 37 seconds e.g.?

  4. #4
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,468

    Re: Changing time offset in Excel column

    I'll give you the play by play with comments inserted in the code, so you can preserve them for later use.

    The code will ignore each line that begins with ' so you can leave it in.

    Check the code in the workbook attached.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-06-2015
    Location
    Slovenia
    MS-Off Ver
    Office 2010
    Posts
    3

    Re: Changing time offset in Excel column

    Fantastic!! Thank you )

  6. #6
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,468

    Re: Changing time offset in Excel column

    Glad I could help out.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Changing time zone value in column
    By jarodbarrera in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-08-2013, 07:51 AM
  2. [SOLVED] Changing Color Font based on date/time column
    By greymoore in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-19-2012, 09:32 PM
  3. Changing chart column width to represent time in excel
    By Rach2012 in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 12-07-2012, 06:12 AM
  4. Replies: 2
    Last Post: 10-03-2012, 11:18 AM
  5. Changing Time Values In Column
    By alexmchugh in forum Excel General
    Replies: 5
    Last Post: 05-06-2009, 04:54 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1