How to change System Date format

While working with dates, we face several problems like month coming in dates and dates coming in months.
To avoid these I personally recommends use DD-MMM-YY date format. the shortcut key for the same in Alt+Shift+3. select the date column and use this shortcut key. you will Date like 01-Jan-86.

Excel read dates as the format define in the system. you can check the system format on the bottom right of the task bar.

As you seen my system read date as DD-MMM-YYY.

Now we learn how to change the system date format. We can do this from Control Panel.

Go to Control Pannel.

Select Region.

On Region, in Format Date Tab change DD-MMM-YY in short Date under Date and time formats.

Now click on the Additional settings. On Customize Format click on Date Tab and change DD-MMM-YY in short Date under Date formats.

Now Click OK & you have done. Please put your queries & Comment.

How to enable Drag & Drop in excel

Run a Macro when data entered in a cell

In Excel worksheet do the following :-

1. Click on the Developer tab.
2. Click on the Visual Basic icon.
3. On the left pane window, double click the sheet where you need your code to run.
4. Now, at the top of the code window you will see... (General) with a drop down, and (Declarations) with a drop down.
5. Click the drop down by (General) and select Worksheet.
6. Now in the code window you will see.... Private Sub Worksheet_SelectionChange(ByVal Target As Range)
7. Remove the word "Selection". You want to remove "Selection" because that means when you click on a cell in the worksheet something will happen. You do not want
8. that, you want to enter a value in D10. It should now read...Private Sub Worksheet_Change(ByVal Target As Range)
9. Now this is where you want the code...

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$D$10" Then 

Call MyMacro 

End If 

End Sub 

Delete Blank Rows

If you have a long list of data that contains blank rows that you want to delete, you have several options.

• You could select each row, one by one, and delete them individually. If you have dozens or hundreds of rows, this could be time consuming.
• You could sort the rows alphabetically, which would put all the blank rows together, and then delete that group of blank rows. However, there will be times when you may want to maintain the order of your list, so this would not be an option.
• You could filter the list for blanks, select these rows (i.e. visible cells) and delete them.
• You could use a little-known shortcut to quickly select the blanks in your list and delete them.

This week I will cover option number 4 and next week I will cover option number 3. The first two are self-explanitory and usually are not your best choice, so I won't discuss those.
Suppose you receive a list of data that contains blank rows that you want to remove without affecting the order of the list. This technique will allow you to delete all rows that have blank cells in a particular column. Here's the fastest way to do it.

1. First, before you make any drastic changes to any workbook always make sure you have a backup copy or a recently saved copy in case you need to restore the original data .
2. Now, select the cells in one column from the top of your list to the bottom.
3. Make sure that all the blank cells in this selected range are the rows you want to delete.
4. Press the F5 key on your keyboard (or select Edit, Goto).
5. Click the Special button.
6. Click the Blanks option and click OK. This will select all blank cells in the range you had previously selected.
7. Now choose Edit, Delete, select the Entire Row option and click OK.

If you work with large lists of data in Excel, this tip will save you a lot of time. If you don't work with large lists often, remember this tip anyway, because sooner or later you're going to need this.

All files open in the same window in Microsoft Excel

Issue

I have a problem with Excel 2007. All my files open in the same window.

Solution

• Go to explorer, choose tools, then folder options. Click the File Types tab.
• Scroll down to the XLS extension. Click the Advanced button. Choose "open", then click the edit button.
• At the end of the "Application used" entry, you'll probably see: /e
• After this, add: "%1" (be sure to include the quotes.)
• Then uncheck the "Use DDE" checkbox. Then click OK. (Windows re-checks it at some point for some reason, but it still works)
• Click on OK
• Now when you double-click a spreadsheet, it will open it in a new instance of Excel

Create a reminder- using VBA

This is how you can create reminder in Excel using VBA, these codes helps you to remind when you open your excel workbook

Y: remind me
N : don't remind me

On VBE (ALT + F11) and in the project explorer window, double click on "ThisWorkbook". Paste the code below.

Private Const reminder As Integer = 1
Private reminderNext As Variant
Public Sub RemindMe()
       currentTime = Time
       nextMin = CDate(Format(Time + 1 / (24 * 60), "hh:mm"))

       myrows = Range("A1").CurrentRegion.Rows.Count

        For thisrow = 2 To myrows
              If (Cells(thisrow, "D") = "X") Then

                    thistime = CDate(CDate(Cells(thisrow, "A")) + Cells(thisrow, "B"))

                   If ((thistime >= Now) And (thistime <= Now + 1 * reminder / (24 * 60))) Then task = task & vbCrLf & Cells(thisrow, "C") & " at " & Format(Cells(thisrow, "B"), "hh:mm")
                  End If
               End If
        Next thisrow
If (task <> "") Then MsgBox task

reminderNext = Now + TimeSerial(0, reminder, 0)
Application.OnTime reminderNext, "ThisWorkbook.remindMe",, True

End Sub


Private Sub Workbook_Open()

Call RemindMe

End Sub

Difference between two dates : Datedif Function

DATEDIF Formula is very useful to calculate age or difference between two dates in excel

The DATEDIF formula computes the difference between two dates in a different manners which is very different to just subtraction of two dates like it can calculate  the number of years, months, or days between the two dates. this formula is not available in already defined formulas of excel, look at below snapshot.

 but it can be available by typing DATEDIF.

Now we learn that how to use DATEDIF :

=DATEDIF(Date1, Date2, Interval)

Where:
Date1 is the first date,
Date2 is the second date,
Interval is type to return.

Note :- Date1 should be less than Date2, Otherwise this function will not work

Important Description:-
"m" Months :- Complete calendar months between the dates.
"d" Days :- Number of days between the dates.
"y" Years :- Complete calendar years between the dates.
"ym" Months Excluding Years :- Complete calendar months between the dates as if they were of the same year.
"yd" Days Excluding Years :- Complete calendar days between the dates as if they were of the same year.
"md" Days Excluding Years :- And Months Complete calendar days between the dates as if they were of the same month and same year.

If you are including the Interval string directly within the formula, you must enclose it in double quotes:

=DATEDIF(Date1,Date2,"m")

Supressing Zero Value Components

In typical usage to calculate the number of years, months, and days between two dates, you would use a formula such as the following, where A1 is the start date and B1 is the end date:

=DATEDIF(A1,B1,"y")&" years "&DATEDIF(A1,B1,"ym")&" months "&DATEDIF(A1,B1,"md")
&" days"

This will return a string such as

12 years 8 months 14 days

However, if the number of years and/or months is 0, you'll get a string like

0 years 0 months 14 days

0 years 3 months 14 days

If you want to suppress the 0 values, and return a result such as

8 months 14 days
or
14 days

where the 0-valued components are not displayed, use a formula like the following:

=IF(DATEDIF(A1,B1,"y")=0,"",DATEDIF(A1,B1,"y")&" years ")&IF(DATEDIF(A1,B1,"ym")=0,"",
DATEDIF(A1,B1,"ym")&" months ")&DATEDIF(A1,B1,"md")&" days"

This will display only the date components whose value is greater than 0. The day value will always be displayed, but the year and/or the month value may be suppresed.

Examples

The follow are some examples of the DATEDIF function.

Date1: 1-Jan-2007
Date2: 10-Jan-2007
Interval: d
Result: 9
Explanation:
There are 9, not 10, calendar days between these two dates.

Date1: 1-Jan-2007
Date2: 31-Jan-2007
Interval: m
Result: 0
Explanation:
There 0 complete calendar months between the two dates.

Date1: 1-Jan-2007
Date2: 1-Feb-2007
Interval: m
Result: 1
Explanation:
There are 1 complete months between the two dates.

Date1: 1-Jan-2007
Date2: 28-Feb-2007
Interval: m
Result: 1
Explanation:
There are 1 complete months between the two dates.

Date1: 1-Jan-2007
Date2: 31-Dec-2007
Interval: d
Result: 364
Explanation:
There are 364 days between the two dates.

Date1: 1-Jan-2007
Date2: 31-Jan-2007
Interval: y
Result: 0
Explanation:
There are 0 complete years between the dates

Date1: 1-Jan-2007
Date2: 1-July-2008
Interval: d
Result: 547
Explanation:
There are 547 days between the two dates.

Date1: 1-Jan-2007
Date2: 1-July-2008
Interval: m
Result: 18
Explanation:
There are 18 months between the two dates.

Date1: 1-Jan-2007
Date2: 1-July-2008
Interval: ym
Result: 6
Explanation:
There are 6 months between the two dates if the dates are considered to have the same year. The year it taken from Date1, not Date2. This makes a difference when one year is a leap year. Since 2007 is not a leap year, 29-Feb is not counted. See DATEDIF And Leap Years below.

Date1: 1-Jan-2007
Date2: 1-July-2008
Interval: yd
Result: 181
Explanation:
There are 181 days between the dates if the dates are considered to have the same year. The year it taken from Date1, not Date2. This makes a difference when one year is a leap year. Since 2007 is not a leap year, 29-Feb is not counted. See DATEDIF And Leap Years below.

Date1: 1-Jan-2008
Date2: 1-July-2009
Interval: yd
Result: 182
Explanation:
There are 182 days between the dates if the dates are considered to have the same year. This result is 182 not 181 since Date1 is a leap year and thus 29-Feb is counted.

Date1: 1-Jan-2007
Date2: 31-Jan-2007
Interval: md
Result: 30
Explanation:
There are 30 days between the 1st and 31st of the dates when both dates' month and years are considered to be the same.

DATEDIF And Leap Years
When calculating date intervals, DATEDIF uses the year of Date1, not Date2 when calculating the yd, ym and md intervals. For example,

=DATEDIF(Date1,Date2,"md")

returns 28 for Date1 = 1-Feb-2007 and Date2 = 1-March-2009. Since Date1 is not a leap year, the date 29-Feb is not counted. But the same formula with Date1 = 1-Feb-2008 returns 29, since Date1 is a leap year and therefore the date 29-Feb is counted.

Calculating Age

You can use the DATEDIF to calculate a person's age. For example, the formula below will calculate the age of a person as of the current date, where BirthDate is the person's date of birth.

=DATEDIF(BirthDate,TODAY(),"y")&" years "&DATEDIF(BirthDate,TODAY(),"ym")&" months "&DATEDIF(BirthDate,TODAY(),"md")&" days"

If you like this post, please like it. for any query or feedback please mention it on comment.
Thank You