Time Slide Chart In Excel

Recently out of curiosity, I wanted to explore a time series data relationship with itself visually on a varying time scale. Hence, I wanted a time series plotted twice on the same chart, but one being flexible (sliding) to slide upon another (fixed).

These are the series of the following charts, I wanted to build in which it is clear that the Value (Blue) is fixed upon which the sliding series is the Slide Value (Grey):


 


 


 


 



The attached spreadsheet illustrates the complete mechanism of building the same, it uses couple of excel formulas and a form control slider. The slider in the workbook allows the flexibility to slide the time series in both forward and backward equally to the original count of the series points available. Additionally in spreadsheet there are no macros, all the functionality is built via default excel provided formulas and also had to use couple of na() formulas for chart to appear as required.

This technique can also be used to analyse additional statistics based upon sliding series. E.g. Max, Min, Average, Standard Deviation and more…

Feel free to contact me for more info upon its design.

Download:


Download solution

Meeting Roomss... Lets book them - Outlook Meeting Room Finder

Hi today i would like to introduce to a very handy tool which me and my office personnel's are quite linking.
Recently i joined my new office and its quite beautiful, but i soon found big beautiful things have some problems which VBA can solve in many times. 

The issue is we have 20-30 meeting rooms in the office and they all can be booked via sending the appointment item to a certain patterned email address each unique for individual meeting room's.

To cut short the story this tool analyses all the meeting room's shared calender selected on the basis of the patterned email address assigned to each one of them. And it collate a list scanning those shared calenders illustrating the time periods between which the required time slot (duration input by user in mins) can be booked and in which meeting room the appointment can be booked.

This entire code is written in VBA within outlook. Give this handy tool a check and alter it to your requirements and make it work for you so that you are never late in you next appointment.

Also the code includes a feature for minimizing the user form in VBA.




Download:

Download solution


References:
Outlook Calender Scan: http://www.vbaexpress.com/kb/getarticle.php?kb_id=671
UserForm Minimize: http://www.vbaexpress.com/kb/getarticle.php?kb_id=165

Let outlook do the thinking: Time based header for outlook

              Myself, being lazy tend to respect the people with greeting appropriate to the time of the day, like Good Morning, Good Afternoon & Good Evening.

   But keeping track of code, time, life and every thing else takes a toll and some times i try to automate these bits as much as possbile to simplify my life.
                  To cut short i desgined a macro which could populate the outlook text for new email with correct headers based on the time of day with the shortcut "Ctrl+J"

To accomplish this

1. Start you outlook..
2. Open a new email interface, press Alt+F11 on you key board to power up your VBA editor.

Navigate to location "Normal" as shown in the image below:



Insert a module named "CustomTextMacro" (any thing you like as it personal choice) 
In that module Type(if you like)/CopyPaste in the following peice of code:

Sub InsertCustomText_Mail()
Selection.HomeKey Unit:=wdStory
Selection.Font.Color = wdColorDarkBlue
If TimeValue(Time()) <= TimeValue("12:00:00") Then
Selection.TypeText Text:="Good Morning,"
ElseIf TimeValue(Time()) <= TimeValue("16:00:00") Then
Selection.TypeText Text:="Good Afternoon,"
ElseIf TimeValue(Time()) > TimeValue("16:00:00") Then
Selection.TypeText Text:="Good Evening,"
End If

Selection.TypeParagraph
Selection.TypeParagraph
Selection.TypeParagraph
Selection.TypeParagraph
Selection.TypeParagraph
Selection.TypeText Text:="Thank you"
Selection.TypeParagraph
Selection.TypeText Text:="Regards"
Selection.TypeParagraph
Selection.TypeText Text:="Amol Pandey"
Selection.TypeParagraph
Selection.TypeParagraph
End Sub

3. Then once completed, navigate to following location as shown in the image:



clicking on it will bring the following window:




then navigate to the Keyboard section of the window and click on it to bring on the following next window:




In this window we will find our macro listed and assigning the macro the required shortcut keys will accomplish the job and, there we have out complete automated solution for the custom outlook header.

Just press the magical shortcut, and there you go on your new email you have the custom header based correctly on the times of the day...

Hope it simplifies your lives as mine...