+ Reply to Thread
Results 1 to 9 of 9

Need help in preparing Summary report through excel vba

  1. #1
    Registered User
    Join Date
    05-09-2012
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    10

    Need help in preparing Summary report through excel vba

    I need a help in preparing a summary report of regular intervals through VBA. Please note the time in the first column of the source data , below date is in IST format and the output needs to be in CST. The difference is in 11:30 hrs(-), i.e., the output time should be 11:30 hours shorter. To illustrate, the data for 6/19/2012 for Agent Name & ID: Mridul Aggarwal - 4758, at 21:45 is actually 10:15 for the same date. Whereas for 6/20/2012, at 00:45, the data is actually for 6/19/2012 13:15 in output.

    PFA the source data in sheet Agent Wise Raw WE ; 24th June and Individual which is the output.

    I would appreciate if you can also send in the algorithm.

    Thanks in advance,
    Compumast
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    05-09-2012
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Need help in preparing Summary report through excel vba

    Am i talking something stupid that no one is able to help here? Please pardon me for some unknown mistake, if I have made any. Would appreciate a quick help! Thanks and regards,compumast

  3. #3
    Registered User
    Join Date
    06-20-2012
    Location
    Melbourne Australia
    MS-Off Ver
    Excel 2010
    Posts
    75

    Re: Need help in preparing Summary report through excel vba

    Hello compumast,

    Excuse me for my ignorance but what is CST format? Also, where and how do you want use the CST date format?

    Cheers,
    Diana

  4. #4
    Registered User
    Join Date
    05-09-2012
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Need help in preparing Summary report through excel vba

    Hi Diana,

    Thank you so much for querying. CST is the Central Standard Time for USA. All I need is just same data, with time in first column minus 11:30 hrs.

    The complication involved is just the time zone difference. The time in source data is in IST (+11:30 hrs), and the desired output should be in CST (-11:30 hrs from the source data). This goes for the first column data. For e.g. A29 should come out as 6/19/2012 13:15 hours in final report.

  5. #5
    Registered User
    Join Date
    06-20-2012
    Location
    Melbourne Australia
    MS-Off Ver
    Excel 2010
    Posts
    75

    Re: Need help in preparing Summary report through excel vba

    Hello,

    Try this for your CST time =A17-"11:30:00"

    Cheers,
    Diana

    If I've been of help, click the star on the left.

  6. #6
    Registered User
    Join Date
    05-09-2012
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Need help in preparing Summary report through excel vba

    Hi Diana,

    Thank you so much for the reply. However, As i am in the learning phase for VBA, I would appreciate if you can help me out with the VBA code to convert this entire sheet in destination just with the time subtracted and adjusted in previous day, if required.

    Thanks and regards,
    compumast

  7. #7
    Registered User
    Join Date
    06-20-2012
    Location
    Melbourne Australia
    MS-Off Ver
    Excel 2010
    Posts
    75

    Re: Need help in preparing Summary report through excel vba

    Sorry compumast,

    Perhaps someone else can help with the VBA code you are after.

    Cheers,
    Diana

  8. #8
    Registered User
    Join Date
    05-09-2012
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Need help in preparing Summary report through excel vba

    Thats perfectly ok, Diana. However, I am really thankful for at least trying to help me out.
    Cheers,
    Compumast

  9. #9
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,490

    Re: Need help in preparing Summary report through excel vba

    If the date and time in column A is rearranged as shown in sheet "Agent Wise Raw WE ; 24th Ju (2)" Time can be reduced by 11:30 hours by running macro.

    Macro is available.
    Sheet is now is showing date and time after running macro.
    File attached.
    Attached Files Attached Files

+ 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