+ Reply to Thread
Results 1 to 17 of 17

Macro for converting Time Zone

Hybrid View

humacdeep Macro for converting Time Zone 09-20-2011, 06:42 PM
Leith Ross Re: Macro for converting Time... 09-20-2011, 09:12 PM
humacdeep Re: Macro for converting Time... 09-21-2011, 08:13 AM
arthurbr Re: Macro for converting Time... 09-21-2011, 08:53 AM
humacdeep Re: Macro for converting Time... 09-21-2011, 09:26 AM
arthurbr Re: Macro for converting Time... 09-21-2011, 09:50 AM
Leith Ross Re: Macro for converting Time... 09-21-2011, 04:07 PM
humacdeep Re: Macro for converting Time... 09-21-2011, 04:21 PM
Leith Ross Re: Macro for converting Time... 09-21-2011, 04:28 PM
humacdeep Re: Macro for converting Time... 09-21-2011, 06:03 PM
Leith Ross Re: Macro for converting Time... 09-21-2011, 06:05 PM
  1. #1
    Forum Contributor
    Join Date
    09-10-2011
    Location
    Chicago
    MS-Off Ver
    Excel 2007
    Posts
    176

    Macro for converting Time Zone

    Hi,

    I am looking for a macro to convert time from IST to CST. I have attached the spread sheet, in which sheet2 is having IST & Sheet1 is having CST.

    I need a Macro to look at ColumnC, ColumnD & ColumnE of Sheet2 and paste the converted result in sheet1 as shown in the attached sheet. It need to look till the last cell of ColumnA every time I run this, as the data is not constant. If any of the cell in empty then it need to copy empty cell instead of negative values.

    I have used the following formula for converting the time

    
    =Sheet2!C2-TIME(10,30,0)
    Thanks in advance for all your help!!

    Regards,
    Humac
    Attached Files Attached Files
    Last edited by humacdeep; 09-21-2011 at 06:04 PM.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Macro for converting Time Zone

    Hello humacdeep,

    The macro below has been added to a button on Sheet1. This will fill the table with the formulae to display the times.

    ' Thread:  http://www.excelforum.com/excel-programming/793159-macro-for-converting-time-zone.html
    ' Poster:  humacdeep
    ' Written: September 20, 2011
    ' Author:  Leith Ross
    
    Sub UpdateTimeTable()
    
      Dim Cell As Range
      Dim Rng As Range
      Dim Wks As Worksheet
          
            Set TimeTable = Worksheets("Sheet1").Range("C1").CurrentRegion.Offset(1, 0)
            Set TimeTable = TimeTable.Resize(RowSize:=TimeTable.Rows.Count - 1)
            
                TimeTable.ClearContents
                
                TimeTable.Cells(1, 1).Formula = "=IF(Sheet2!C2-TIME(10,30,0 )>0,Sheet2!C2-TIME(10,30,0 ),"""")"
                TimeTable.Cells(1, 2).Formula = "=IF(Sheet2!D2-TIME(10,30,0 )>0,Sheet2!D2-TIME(10,30,0 ),"""")"
                TimeTable.Cells(1, 3).Formula = "=IF(Sheet2!E2-TIME(10,30,0 )>0,Sheet2!E2-TIME(10,30,0 ),"""")"
                
                TimeTable.Rows(1).Cells.AutoFill TimeTable.Rows.Cells
    End Sub
    Attached Files Attached Files
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Forum Contributor
    Join Date
    09-10-2011
    Location
    Chicago
    MS-Off Ver
    Excel 2007
    Posts
    176

    Re: Macro for converting Time Zone

    Hi Leith,

    The code is not working as desired. Sorry if my earlier request is not clear.

    I have attahed the modified sheet, in sheet1 the result is from Macro, Sheet2 has orginal data, Sheet3 is having the desired result.

    Regards,
    Humac
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Macro for converting Time Zone

    What are IST and CST ?

  5. #5
    Forum Contributor
    Join Date
    09-10-2011
    Location
    Chicago
    MS-Off Ver
    Excel 2007
    Posts
    176

    Re: Macro for converting Time Zone

    Hi Arthur,

    IST is Indian Standard Time & CST is Central Standard Time.

    Regards,
    Humac

  6. #6
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Macro for converting Time Zone

    Thx,
    this will help the members unaware of that information.

  7. #7
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Macro for converting Time Zone

    Hello humacdeep,

    I revised the macro and added it to the workbook below. Here is the revised code.
    Sub UpdateTimeTable()
    
      Dim Cell As Range
      Dim Rng As Range
      Dim TimeTable As Range
      Dim Wks As Worksheet
      
          Set Wks = Worksheets("Sheet2")
          Set Rng = Wks.UsedRange
          
            Set TimeTable = Worksheets("Sheet1").Range("A1").CurrentRegion.Offset(1, 2)
            Set TimeTable = TimeTable.Resize(TimeTable.Rows.Count - 1, 3)
            
                TimeTable.Cells.ClearContents
                
                TimeTable.Cells(1, 1).Formula = "=IF(Sheet2!C2-TIME(10,30,0 )>0,Sheet2!C2-TIME(10,30,0 ),"""")"
                TimeTable.Cells(1, 2).Formula = "=IF(Sheet2!D2-TIME(10,30,0 )>0,Sheet2!D2-TIME(10,30,0 ),"""")"
                TimeTable.Cells(1, 3).Formula = "=IF(Sheet2!E2-TIME(10,30,0 )>0,Sheet2!E2-TIME(10,30,0 ),"""")"
                
                TimeTable.Rows(1).Cells.AutoFill TimeTable.Rows.Cells
                
    End Sub
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    09-10-2011
    Location
    Chicago
    MS-Off Ver
    Excel 2007
    Posts
    176

    Re: Macro for converting Time Zone

    Thanks for the code Leith!!

    It is working fine if I copy the data in Sheet1 from Sheet2. I tried deleting data in sheet1 and run the Macro then I am getting error 400.

    Does this code copy the data in sheet1 from sheet2 or Do I need to manually paste it in sheet1 and run the Macro?

    Regards,
    Humac

  9. #9
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Macro for converting Time Zone

    Hello humacdeep,

    I assumed the table already existed on Sheet1 and the macro expects the same. It simply adds the needed formulas.

  10. #10
    Forum Contributor
    Join Date
    09-10-2011
    Location
    Chicago
    MS-Off Ver
    Excel 2007
    Posts
    176

    Re: Macro for converting Time Zone

    Thanks for the reply!!

    Is it possible to get the result directly in Sheet1 from Sheet2?

    Regards,
    Humac

  11. #11
    Forum Contributor
    Join Date
    09-10-2011
    Location
    Chicago
    MS-Off Ver
    Excel 2007
    Posts
    176

    Re: Macro for converting Time Zone

    Thanks Leith for your time & patience!!

    Its really working like a charm

  12. #12
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Macro for converting Time Zone

    Hello humacdeep,

    You're welcome. Glad we got it straightened out and working right.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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