Need help in preparing Summary report through excel vba

[compumast]

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

[compumast]

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

[dmang1]

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

[compumast]

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.

[dmang1]

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.

[compumast]

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

[dmang1]

Sorry compumast,

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

Cheers,
Diana

[compumast]

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

[kvsrinivasamurthy]

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.