writing a macro to convert date time in a sheet

[bz6djs]

Hi all, I will try to make my question easily understood (which actually means I understand what I am asking )
I have a spread sheet with a list of open tickets that were exported. Say rows 2 - 50. Column G shows open time. (row 1 is a header)
Column H from above shows update time.
I then export a list of closed tickets and tack that on the bottom of the open cases. Say rows 51-70 Column I shows closed time. Closed time is column I.

My question is this (being new to macros) I want to insert a blank column to the right of G and put in this formula in that blank column to the right of my date time column G above =g2-(9.5/24) and have that same reference formula copy from 1-50.(the # of cases is not always the same 50 today could be 70 tomorrow or 35)

I would do the exact same steps for column H above =h2-(9.5/24)

Next step is to do the same for Column I but not sure how I get the focus on row 51 (since first 50 rows are blank as they are not closed) and again goto bottom of the data (with varying amounts each time it is run) and paste that same formula.). This is where I have a problem as I do not know what row my closed data starts on =I??-(9.5/24) - is there a way to say go to first item in this column and move to right and paste formula?

Now I will copy (what is temp column H) and PASTE VALUES only to col G and delete col H
once that is deleted
I will copy (what is temp column I) and PASTE VALUES only to col H and delete col I
and finally do the same for column I ...

I hope that all makes sense and someone can help me with my questions ... interesting stuff but not familiar enough with all the commands
same on

[Jocote46]

please attached a sample file that simulates the data and the desire result. it would help a lot.

[bz6djs]

OK not sure what I am doing wrong but I do not have the ability, using normal and advanced modes, to be able to attach a picture or a file.

I have created a snippet from the original file so you can see it exactly. I have changed the data so as not to expose ... the 3 columns I am interested in are G H I
as these are the date time columns.

I am going to try and paste a link in here ... I will upload the files (graphic and xlsx) to my ftp space and post the link in a few mins

[bz6djs]

the links are
http://www.craftytaylors.com/sample.jpg
http://www.craftytaylors.com/sample_data.xlsx

Thank you for taking a look

[Leith Ross]

Hello bz6djs,

As I understand your first post, the date/time values in columns "G:I", starting in row 2 down to the last row with data, need to have 9.5 hours subtracted from the cell if it is not empty. If this correct then the following macro will work. It has been added to the attached workbook.

To run the macro you can use the keys Alt+F8 to display the Macro Dialog. Click on UpdateDateAndTime and then click Run.

Please Login or Register  to view this content.

[bz6djs]

Please disregard my last post ... now that I have re-read what was written and tested it ... THIS IS PERFECT!! Thank You So Much!

[Leith Ross]

Hello bz6djs,

You're welcome. I am going to mark this post as solved.