Image

How Time Formulas work in Excel

May 4, 2018 By Jeet Shah in Date & Time No Comments Tags: calculation, duration, function, hour, minute, second, time

Very often, confusion prevails when it comes to calculations based on Time in Excel. Any calculation gives out seemingly random numbers and you don’t know what to do with it. Read on to really understand how Time works in Excel.

Types of Time

There are 2 main ways to interpret time. First one is Time as it shows on the clock – 1 PM, Noon, 5 PM, etc. This is usually the answer to questions like ‘What time is it now?’ or ‘When is the dinner to be served today?’

The second kind of interpretation is time spent – 1 hour, 3 days, a month, etc. This is usually the answer to questions like ‘How long is the movie?’ and ‘How long do I bake it for?’. A more appropriate word for this is duration.

We will look at how to use both these interpretations in Excel.

First, on a Date

All dates in Excel are stored as serial numbers. To see this, put in today’s date into a cell and then change its number format to General (in Home Tab, Number format group). You will see the date changes to a serial number. This serial number starts from 1-Jan-1900 – which has the serial number of 1. 2-Jan-1900 will have a serial number of 2; and it continues like this. The serial number for today’s date is 43200. This simply means that it has been 43,200 days since 1-Jan-1900. If you type 43200 in Excel and press Ctrl + Shift + 3; you will see the date this article was first written.

General Number Format

Because of this, you can do mathematical calculations on dates. You can try adding or subtracting days from some date easily. A similar logic is used for Time.

How Time works in Excel

If you understand how it is stored in Excel, you will have no more problems with it! Let me explain.

Time is stored as a decimal value.

To understand this better, type in 6:00 AM in a cell and change the number format of the cell to general. You will see the value stored by Excel is 0.25.

This is because in a single 24-hour day; 6:00 AM would be one quarter of a day. As we already saw, one day is stored as 1 in Excel. Therefore, a quarter of 1 is 0.25! This proves to you that Time is stored in as a value between 0 and 1. Going by this logic, 12 noon is 0.5 – type it in a cell and change the number format to Time.

This is where the confusion usually happens – 9:00 AM is 0.375 and 3:30 PM is 0.645833333. In other words, time is a fraction of 1. To see the decimal value of an hour, divide 1 by 24.

How Duration works in Excel

Here is some good news – there is no separate system for duration. The decimal values stored in Excel remain the same. All that differs is the number format. This means that to Excel 6 AM and 6 hours is the same. This is because if you try and write 6 AM into a cell, what it means is 6 hours since midnight. In decimals, duration will still stay 0.25 – a quarter of a day. If you say 30 hours, it means one full day plus 6 hours. This is exactly how Excel sees duration too – as 1.25.

If you type out 6:00 in a cell and go to custom number format, the number format will be set to hh:mm. But try typing out 30 hours and check the number format – it will be set to [h]:mm:ss.

As far as duration is concerned, only these two need to be understood. The thing is that there are only 24 hours in a day. Now, the hh:mm format follows clock time limitations. So, it cannot show any duration more than 23 hours and 59 minutes. The 24^th hour is midnight – which is stored as zero by Excel.

Therefore, to show any duration that is more than 23 hours and 59 minutes, the number format [h]:mm:ss is used. The square brackets tell Excel that this number is not to be displayed as clock duration, but as duration – in general. Look carefully at the date in the formula bar:

Different Formats – a Comparison of different formats for 0.25

Negative Time Duration

You cannot have a negative unit of time like -6:00 AM, but duration can be negative or positive. For example, in a three-hour exam, you took only 2.5 hours to finish all your answers. This can also be expressed as -0.5 hours, if you are looking at how soon you finished (time taken minus the total time). But try doing this in Excel with a duration format applied. The cell will just show ### – which is Excel’s way of indicating a display error. There are 2 ways to fix this – either by changing the date settings for the workbook [A] or by using a function [B].

A] On the second Date

There is a second date system that you can switch to – the 1904 date system. It has the following 2 features:

The difference between the two date systems is in the start of the serial number. In this system, serial number 0 is 1-Jan-1904. Serial number 1 is 2-Jan-1904; and so on.
This system supports negative durations.

To switch to this system, go to Excel options > Advanced > When Calculating this workbook > Use 1904 date system. The only problem with this is that all the dates in the workbook change by 4 years. So, you need to do this beforehand. To remedy this, we can use a function.

The 1904 Date System

B] Using a function for negative time durations

The trick is to fool Excel into calculating without the negative sign. This is easily done by using the ABS function. Its function is to ignore the signs attached to a number. If we plug our calculation right into this, it works well enough. The display error is resolved.

Negative Durations – ABS function

But what if you want to see the negative sign before this duration? The solution would be to use something like this:

Negative Duration with Sign

Caution: This will be now become a ‘text’ cell. Which means you cannot do calculations directly on this.

An Example

This is sample data from a race with player names, start & finish times and duration. The duration is simply end time less start time; with a duration number format applied.

Race Scores

Before calculating the leads, the data must be sorted from the shortest to longest duration. The lead for the first one will be zero. The second one will need a formula that calculates how many seconds behind the first, and so on.

Leads – how many seconds to gain next place?

As you can see, the negative durations are not displayed. To fix this, use:

="-"&TEXT(ABS(D5-D4),"m:ss")

Here, we do not need hours at all only minutes, if any and seconds. Therefore, the number format is changed to just include minutes. Here, it does not matter if we put square brackets around the [m] as there is no possibility of having more than 59 minutes of lead. Unless this was for the Dakar Rally!

Leads with Negative Duration

Easy right? Here is a question – what do you think the formula should look like to get the lead from the first one? Post your answers in the comments below!

What do you think?

The Time function in Excel

To make life easier, there is a function just for time. It just requires 3 components to return a time:

Hour
Minute
Second

The Time function

Using this function makes it a little difficult to enter in time; but it allows for easier calculations. For example, if you need to add a couple of hours to 9:32:28 AM; the function would look like: (see screenshot above)

=TIME(D4+2,E4,F4)

Here, only for the hour component, we are adding 2. Likewise, it becomes very easy to add / subtract minutes or seconds too.

Functions related to the components of time

The 3 components of time are also independent functions. All 3 require only a time value. They will then return only the hours, minutes or seconds as a whole number in a 24-hour format. Because it’s a whole number, duration number formats cannot be applied directly on them. For example, if you try to use the duration number formatting on 14 hours – it will give out a value of 336:00:00. So, the most reliable way to calculate durations would be as we did earlier in this post.

Extracting Hours, Minutes and Seconds

How to use the Time functions

Let’s take the example of a simple production cycle. Product X is made in 5 stages, each taking different amounts of time to manufacture / process. If the production starts at 10:00 AM, the end times for each stage are to be calculated.

Time Calculation Example

Here, we are using the hour and minute functions to first extract only hour and only minutes from the start time. Their respective durations are then added to them individually. Both these calculations are used within the Time function. Therefore, we directly see the calculated end time. Note that seconds are not counted and are set to zero.

This is pretty much everything you will ever need to tackle time in Excel.

Keep Excel-ing!

Excel Tables vs Excel Data Range

March 6, 2018 By Jeet Shah in Best Practices, Cell & Data Formatting, Named Ranges No Comments

A range of cells filled with data is generally called a table. So, you put ‘All Borders’ around your data and think it is a table no? Maybe also format the headers a bit. But, this is just formatting applied to a normal Excel data range. Sure, when you print it out, it looks like a table. Nothing wrong with this, but you are missing out on a lot.

We have a simple dataset of fruit sales over a period of time. Each sale is recorded with the date, fruit sold, price, quantity and discount percentage.

Base Data

Once you convert a range into a table, it opens many new features just for it. To do this, just click somewhere inside the data and press Ctrl + T to convert it to an Excel Table. Tick the box if your data has headers and press OK or Enter.

Create Table Options

Created Table

You will see this immediately formats the data. Also, there is a new Table Design tab on the ribbon. The first option on the far left, there is an option to rename the table. This is especially useful when there are multiple tables in the workbook.

Design Tab

The features available with Excel Tables:

1. Easy Formatting

Ready-made styles are applied automatically to an Excel table. They can be changed easily using the Table Design tab. You can also define your own style. Automatic banding of alternate rows / columns is a big time-saver.

Easy Formatting

2. Filter & Sort Buttons

Did you notice that filters are applied automatically, ready to use? You can easily turn them off in the Design Tab. The keyboard shortcut to toggle this is Ctrl + Shift + L

3. Auto-Freeze Headers

Scroll down to see the Table Headers replace the column names (A, B, C, …). No need to use the Freeze Top Row feature.

Auto Freeze Headers

4. Single-Click Selections

You can do this by clicking the top and left sides of the table (not on usual Excel row columns). Useful for quickly selecting rows or columns. Clicking on the Top–Left corner of the table will select the entire table. When you take your cursor near these areas, it will change into a small black arrow. Clicking on the Excel row (1,2,3…) or column (A,B,C…) headers will select the whole sheet; not just data in the table.

Alternatively, you can use keyboard shortcuts. With the cursor in your data try these:

Ctrl + A will select only the data without the headers. Pressing it again will select the entire Table.
Ctrl + Space will select the entire column in the table
Shift + Space will select the entire row in the table

Easy Selections

5. Easy Rearrangement

Holding the border of a selection, you can drag and drop the data to rearrange very easily.

Easy Rearrangement

6. Easy Growth of Data

This can be done by simply adding new data below or to the right; either by typing or pasting. Alternatively, while entering data manually, the Tab key is very useful to enter data and navigate. It will keep moving the cursor to the right till it comes to the last cell of that row. Then it will move to the first cell of the next row. When you reach the end of the table, it will just add a new row!

Easy Growth

7. Special Printing Option

Special option to select only table while printing. Using this will ignore all the data around selected table.

Printing Option

8. Automatically Copy formulas.

Entering formulas anywhere in an empty column will auto-copy to entire column. No need to drag down using the fill handle.

9. Structured References

Structured References are basically ‘verbose’ cell references. Instead of column and row (A1), it will read as (Fruit_Sales[Date]). There are 2 advantages to this. Firstly, formulas become very ‘readable’. Instead of showing =D2*E2; a structured reference will show =Price*Qty. Second advantage is that the names are fixed. Which means the column will always be called by its header i.e. they are fixed (absolute) references. No need to go crazy with dollar signs.

Structured References and Auto-Copy Formulas

10. Dynamic Named Range

Having fixed names also means that whenever new data is added to the column, it still stays part of the column. There is no need to update any reference like changing A1:A5 to A1:A10 because 5 new rows of data are added. This means the data will keep growing; but the Table name will remain the same. So, any references to the Table will not have to be updated every time new data is added. For example, if a pivot table is based on an Excel Table, it will keep seeing all the new data that’s been added.

Fixed Table Name

Fixed Table Name – After Growth

A few common features where this can be very useful:

Charts
Conditional formats
Data Validation lists

11. Pivot Tables

A big disadvantage of pivot tables is that new data is not included automatically. In cases with regularly growing data, it becomes a chore to keep changing the source data of the pivot table. But, if the source of data is from an Excel Table, any growth of data will not change the name of the table. Therefore, only the refresh button needs to be used to feed all the new data into the pivot tables. This is especially useful; and recommended when there are multiple pivot tables involved.

Refresh Pivot Tables

12. Special Total Row

What’s so special about totals? This:

The total row will always stay below the last row of data.
Multiple kinds of calculations like counts, averages, etc.
Use in multiple columns. E.g. You can have a grand total & a count of all the items.
Will be excluded from pivot tables automatically.

Special Total Row

13. Insert Slicer

Add slicer filters to data without making pivot tables or any data connections. A slicer is basically a panel that allows you to filter values in a column. It can be placed anywhere independently of the table. You can click on the buttons to show only those items.

Slicer

So, as you can see, a lot of functionality is added when you define an Excel Table. It helps save a lot of time and headache in the long run. Also, it becomes easier to do both, data entry and calculations. Once the format of your dataset is fixed, it is a good idea to convert it into an Excel Table. One last tip is that there is also a way to convert an Excel Table into a normal Excel range by using the ‘Convert to Range’ button in the Table Design tab!

Allow only 10 digit numeric mobile number in Excel using Custom Data validation

August 18, 2017 By rushabhs in Data Validation No Comments

Mobile numbers are always numeric and of a certain length. You want to ensure data entered is exactly as per that.

In India, mobile numbers are 10 digits. Meaning if any mobile number that’s entered isn’t exactly 10 digits, it’s not a correct data entry.

Similarly, in other countries, mobile number length’s vary. Whether you’re in India, USA, UK Australia, wherever, this method will be extremely useful for you.

What we want

Thus, we want be build a data validation that suffices 2 conditions: –

Number entered must be Numeric
Length of the number must be exactly 10 digits (for Indian mobile number)

Which 3 functions will be used

=ISNUMBER
this function will ensure that the number entered in only numeric, no text, no dates
=LEN
this function will ensure 10 digits only
=AND
to ensure both the conditions are met together in ANDing
To know how AND function works, click and read this blog post

Steps to create the custom data validation using these 3 functions

Create a column for Mobile numbers (say F column)
Select all the cells (except the header)
Select column without header
Under Data tab -> Data Validation -> Allow -> Select ‘Custom’
Data tab

Custom
Enter the formula
=AND(ISNUMBER($F2),LEN($F2))
Your Data Validation is done and it will only allow 10 digit – numeric data entry

Logic behind how it works

Formula

The F2 is simply because we are using the F column for the mobile numbers column, 2 is the row from where the data entry starts.
$F is, we are asking Excel to check only the F column for the 2 conditions.
=ISNUMBER($F2)
this checks whether the data entered is numeric or not. Internally, it gives a boolean value as TRUE or FALSE. So if the data entered in numeric, it will be internally TRUE, else FALSE.
=LEN($F2)=10
this checks whether the data entered in 10 digits or not. Internally, this too gives a boolean value as TRUE or FALSE. So if the data entered in 10 digits, it will be internally TRUE, else FALSE.
=AND
ANDing means, both conditions should be TRUE. So only when both the boolean results are TRUE, the data validation will allow the data entered by the user. If any one value is FALSE, the data validation will reject the data entered by the user.

See a step-by-step video

Difference between SUM, SUMIF, SUMIFS, DSUM

December 29, 2016 By rushabhs in Formulas No Comments

Whenever you type =SUM in a cell in Excel, you get a lot of functions starting with Sum…

In this blog post, we will be focussing on 4 of them – SUM, SUMIF, SUMIFS, DSUM.

Sum 4 functions

Each of these functions has its own capability for Summing up numbers. Let’s go one by one.

We will be using a sample data set of Umbrella sales (1000 rows of data), to understand each of these functions in detail. Thus the data range is, A1 : J1001.

Umbrella Sales Data

SUM

Sum function will get you a sum from multiple individual cells, a single range, or even multiple ranges in 1 shot.

Sum of all Quantity (I column)

The formula will be =SUM(I2:I1001)

Sum of all Totals (J column)

The formula will be =SUM(J2:J1001)

SUMIF

Sumif function gets you a sum based on 1 condition (only).

Sum of all Quantities of only Blue umbrellas

The formula will be =SUMIF(G2:G1001,”blue”,I2:I1001)

=SUMIF (Range, Criteria, Sum_Range)

Criteria – Blue (the actual condition).
- The criteria is not case sensitive. The “blue” can be written in upper case or small caps, doesn’t matter.
- Since the criteria is text, it needs the ” “. If the criteria was a number, it wouldn’t require the ” “. Date will require ” “.
Range – The column where Blue, the criteria, resides. Thus Color (G) column in this case.
Sum_Range – Sum of what? Quantity, Total? since we need the Sum of Quantities, we choose Qty (I) column.
The order can’t be messed up – First the Range, then Criteria, then the Sum_Range

SUMIFS

Sumifs (notice the ‘s’) gets you a sum based on multiple conditions. But it also works with a single condition as well!

Sum of all Quantities of Blue, Small size Umbrellas

The formula will be =SUMIFS(I2:I1001,G2:G1001,”blue”,F2:F1001,”small”)

=SUMIFS (Sum_Range, Range 1, Criteria 1, Range 2, Criteria 2, …, Range n, Criteria n)

Observe carefully that in the case of SUMIFS, the Sum_Range comes first!
Following after, Range & Criteria of each condition, similar to SUMIF
Note that all conditions in SUMIFS will always get ANDed and never ORed
Thus, no 2 conditions should fall in the same column. If they do, your formula will be a little different, explained below in the next example.
The order can’t be messed up – Sum_Range first!
As stated above too, SUMIFS is capable of handling a single condition too. I thus never use SUMIF, i only use SUMIFS.

Sum of all Quantities of Blue + Yellow Umbrellas

The formula will be =SUMIFS(I2:I1001,G2:G1001,”blue”)+SUMIFS(I2:I1001,G2:G1001,”yellow”)

Observe that in this case, both the conditions are falling into the same Color column.
Thus, both conditions need to be ORed.
This can easily be done by using
- SUMIF + SUMIF OR
- SUMIFS + SUMIFS

DSUM

Now, what if you needed to have a summation of quantities of Blue, Black, Yellow and Pink colors!

What is there are 40 different colors of umbrellas that you are selling, and you want a total of 20 of those colors!

Instead of writing =SUMIF + SUMIF + SUMIF + SUMIF + ….. 20 times, simply use DSUM !!!

=DUM let’s you create your condition outside the formula, making it very dynamic in nature, and easy to create.

Sum of quantities of Blue + Yellow + Black umbrellas

Creating the Condition (Criteria) outside

DSUM criteria outside

Write the column Header first (exactly same as in the data), in this case ‘Color’.

Below that, write each of the colors, one below the other.

When you write the criteria’s one below the other, it means OR-ing.

The formula will be =DSUM(A1:J1001,I1,M21:M24)

=DSUM ( Database, Field, Criteria )

Database – Select the entire data set
Field – Select only the header of the column, of whose SUM you want, in this case the Quantity column, hence I1.
Criteria – Select the grid that have you created outside.

Sum of quantities of (Blue, Small) + (Black, Large) umbrellas

Creating the Condition (Criteria) outside

When you write the criteria’s next to each other, it means AND-ing, and below means OR-ing.

The formula will be =DSUM(A1:J1001,I1,M21:M24)

Four things you can do with Enter key in Excel

November 15, 2016 By rushabhs in Excel Shortcuts, Tips No Comments Tags: Quick Tip

You will save a lot of time on a daily basis by using the Enter key in these 4 ways.

You may think, what is more to learn in using the Enter key in Excel? Read further to know what are the 4 ways and what they can do for you.

Enter (Normal)

When you type anything and press Enter, or even without typing you just press Enter,
– It takes you to the next cell in the next row (1 step down).

Enter (Normal)

Shift + Enter

Normally, when you want to type something, and then go 1 cell up, you end up doing this,

Type – Enter – Up arrow – Up arrow. Rather use Shift +Enter

Thus, when you type anything and press Shift + Enter, or even without typing you just press Shift + Enter,
– It takes you to the next cell in the previous row (1 step Up).

Shift + Enter

Alt + Enter

You maybe using the feature Wrap Text in Excel.

– But using Alt + Enter, you can write in the next line, in the same cell.

This will help in a way that you can decide when you want to next line to start (in the same cell). Wrap doesn’t allow that, it wraps the text automatically based on the width of the column.

Thus, when you type something and press Alt + Enter, you will go to the next line in the same cell. Then once you are done typing, press normal Enter. The entire Row’s height will increase automatically.

Alt + Enter

Ctrl + Enter

Let’s say you want to type something and then make it Bold. Normally we do this,

Type – Enter – Up arrow – Ctrl + B (bold). Rather use Ctrl + Enter and then directly Ctrl + B.

Thus, Ctrl + Enter allows you to stay in the same cell after typing something.

Ctrl + Enter

Do try these out once. They are amazing and very handy to use everyday.

If you have found this useful, leave a comment below!

Keep Excel-ing!

10 Excel Shortcuts that will change the way you work

August 30, 2016 By rushabhs in Excel Shortcuts, Tips No Comments Tags: Excel shortcuts

When you work with Excel, you must avoid using the mouse as much as you can. Using the keyboard only will help enhance your speed a lot, and you will start saving a ton of time!

I understand that remembering Excel shortcuts is a pain, so I have listed below 10 shortcuts which can be remembered easily. I assure you, these are the ones that you will definitely want to use everyday!

Start using these 10 Excel shortcuts first and then once you have been using these on a daily basis, learn more.

I have used a sample data as below to demonstrate all the shortcuts

Select Entire Column
Ctrl + Spacebar
Keep your cursor anywhere in the column that you want to select, and press Ctrl + Spacebar
Insert Column / Row
Ctrl + +
Select the column (with above shortcut) before which you want to insert a new column and press Ctrl + +
Save As
F12
Select column with data
Ctrl + Shift + Down
Keep your cursor at the beginning of your column, and press Ctrl + Shift + Down. It will select the data to a point where the next cell is blank. So if there is a blank cell in between the data, it will select upto a point just before that.
Undo & Redo
Ctrl + Z / Y
Undo if ofcourse to undo something you did by mistake.
Redo and to redo something you just Undo’d!

Deleted the data. Then pressed Undo to get it back

Deleted the data again by pressing Redo
Auto Filter On/off
Ctrl + Shift + L
Sort in Ascending Order
Alt – A – S – A (Excel 2013 and higher)
Alt – A – A (Excel 2007 & 2010)
Keep your cursor anywhere in the column by which you want to sort the data, and hit the shortcut key.
Apply All Borders
Alt – H – B – A
Select the required data that you want to apply borders to, and press Alt – H – B – A
Auto-fit Column Width
Alt – O – C – A
Select the column and then press Alt – O – C – A.

Data is getting cut out visually

After pressing the shortcut
Center Align
Alt – H – A – C
Select the cell(s) that you want to center align and press Alt – H – A – C

AND A BONUS ONE!!!
Go to Previous Sheet / Next Sheet
Ctrl + Page Up / Page Down

VBA Code to Calculate Age from Date of Birth

July 2, 2016 By rushabhs in VBA (Visual Basic for Applications) No Comments

Let’s say you have your Date of Birth entered in Cell A2.

You want the Age in Years to appear in Cell B2.

Age from DOB

Code

‘Finding Age of Patient is displaying in Label

Dim b As Date

b = Sheets(“Sheet1”).Range(“A2”).Value

Dim d As Integer

d1 = Day(Date)
d2 = Day(b)
m1 = Month(Date)
m2 = Month(b)
y1 = Year(Date)
y2 = Year(b)

If m1 > m2 Then
d = y1 – y2
ElseIf (m1 >= m2 And d1 >= d2) Then
d = y1 – y2
Else: d = y1 – y2 – 1
End If

Sheets(“Sheet1”).Range(“B2″).Value = d & ” years”

End Sub

Explanation

Dim b As Date
b = Sheets(“Sheet1”).Range(“A2”).Value

You declare a variable ‘b’ as a Date.
You pick up the value from Cell A2 and put it into b.
d1 = Day(Date)
d2 = Day(b)
m1 = Month(Date)
m2 = Month(b)
y1 = Year(Date)
y2 = Year(b)

d1 = fetch the Day part from Today’s Date
d2 = fetch the Day part from b, that is the Date of Birth
m1 = fetch the Month part from Today’s Date
m2 = fetch the Month part from b, that is the Date of Birth
y1 = fetch the Year part from Today’s Date
y2 = fetch the Year part from b, that is the Date of Birth
If m1 > m2 Then
d = y1 – y2
ElseIf (m1 >= m2 And d1 >= d2) Then
d = y1 – y2
Else: d = y1 – y2 – 1
End If

Logic to calculate no. of years and the value is put in a variable d, which is earlier declared as an integer
Sheets(“Sheet1”).Range(“B2″).Value = d & ” years”
Paste the value of d to cell B2.

How to run VBA code on a protected sheet

May 17, 2016 By rushabhs in VBA (Visual Basic for Applications) No Comments Tags: VBA

You have a sheet with list of Tata cars.

The need is to sort them in ascending order of Model name upon clicking a button.

Data of Cars

The VBA code to sort would be: –

Sub Button1_Click()

Range(“A:C”).Sort , Key1:=Range(“a1”), Order1:=xlAscending, Header:=True

End Sub

When you run this code, it will work perfectly and sort the data.

But if this sheet is protected, then you will get an error like this,

Error

To make it work, you will need to

Give the password alongwith before your code so that the Sheet is first Unprotected with .Unprotect
The Sort code as mentioned earlier
Then .Protect to protect the Sheet again.

Let’s say the Sheet’s password is abc

Correct VBA Code: –

Sub Button1_Click()

Sheets(“Sheet1″).Unprotect Password:=”abc”

Range(“A:C”).Sort , Key1:=Range(“a1”), Order1:=xlAscending, Header:=True

Sheets(“Sheet1″).Protect Password:=”abc”

End Sub

Data Sorted

How to make a dependent drop-down list in Excel

March 8, 2016 By rushabhs in Data Validation, Named Ranges No Comments

Creating a drop-down list in Excel is very easy, using Data Validation.

Click here to see how to create it. Then come back here.

Now we will see how to create a dependent list – a list based on a selection in another list. Thus you will be able to items items based on your selection in another list.

To give you a simple example, we are taking a list of Product Categories where the categories are,

Laptops
Mobile Phones
Televisions

Once you select 1 of the categories from the drop-down, in another cell, you should be able to select a Brand which is under only that particular category.

Categories & Brands

So if you select Laptops, the list of brands to select from in another cell should be

Dell
HP
Lenovo
Apple
Microsoft

Named Ranges

Named Ranges will need to be used to create such a list.

Click below 2 links to understand Named Ranges basics. Then come back here.

Steps: –

Create Named Ranges for the following: –
Ensure that none of your named ranges have any spaces in between 2 words.
- Categories names
  - Select A1:C1 and give a named range, say, ‘Categories’
- Brands for individual categories
  Ensure that the named ranges and the Category names (column headers) should be exactly the same
  - Select A2:A6 and give a named range ‘Laptops’
  - Select B2:B6 and give a named range ‘MobilePhones’ (without a space in between)
  - Select C2:C6 and give a named range ‘Televisions’
Create Category Drop-down lists
1. Select cells where you want to select the category names, E4:E8 in our case (see image below)
2. Go to Data tab -> Data Validation
3. Under Allow, choose List
4. In Source, type =Categories (this is the named range we gave earlier).
5. You will see that you are able to select the Categories in each of the cells, from the drop-down.
Create Brand lists – Dependent lists
1. Select F4:F8 and go the Data Validation, Allow List
2. In Source, type =INDIRECT(E4)
3. A dependent drop-down list is created, based on your selection of the Category.

Creating DropDowns

Indirect function

How did it happen!?

=INDIRECT(E4) goes into cell E4 and checks what is the content of that cell.
If the content is ‘Laptops’, it will go and fetch the entries in the Named Range ‘Laptops’.
If the content is ‘MobilePhones’, it will go and fetch the entries in the Named Range ‘MobilePhones’.
E4 when does below 1 row, becomes E5. Thus Whatever is selected in E5’s drop-down, the dependent list appears in F5. And so on..

Dependent list created

Keep Excel-ing!

Find number of months between 2 dates in Excel

February 3, 2016 By rushabhs in Date & Time, Formulas 6 Comments

To calculate number of months between 2 dates requires =Year and =Month formulas.

Before we get into it, let us first see how to calculate number of days between 2 dates.

To find number of days between 2 dates as super easy!

Simply negate the 2 cells,

Cell A2 has Start Date
Cell B2 has End Date
Simply put the formula in cell C2
=B2 – A2

No of days formula

No. of days Answer

But if you want to calculate no. of Months between 2 dates, create the below Formula

Cell A2 has Start Date
Cell B2 has End Date
Simply put the formula in cell C2
=(YEAR(B2)-YEAR(A2))*12+MONTH(B2)-MONTH(A2)

No. of Months formula

No. of months Answer

Understanding the Fomula

Year (B2) – Year (A2) * 12
- This will calculate no. of years between the 2 dates and multiply it by 12 to get the no. of months
- So if the 2 dates are falling in the same year, the answer to this part will be 0 * 12 = 0
Month (B2) – Month (A2)
- This will calculate no. of months between the 2.
Both values are then added.

Keep Excel-ing!

1 2 3 4 5 >»

Image

Types of Time

First, on a Date

How Time works in Excel

How Duration works in Excel

Negative Time Duration

A] On the second Date

B] Using a function for negative time durations

An Example

The Time function in Excel

Functions related to the components of time

How to use the Time functions

1. Easy Formatting

2. Filter & Sort Buttons

3. Auto-Freeze Headers

4. Single-Click Selections

5. Easy Rearrangement

6. Easy Growth of Data

7. Special Printing Option

8. Automatically Copy formulas.

9. Structured References

10. Dynamic Named Range

11. Pivot Tables

12. Special Total Row

13. Insert Slicer

What we want

Which 3 functions will be used

Steps to create the custom data validation using these 3 functions

Logic behind how it works

See a step-by-step video

SUM

SUMIF

SUMIFS

DSUM

Enter (Normal)

Shift + Enter

Alt + Enter

Ctrl + Enter

AND A BONUS ONE!!!

Code

Explanation

Named Ranges

Steps: –

How did it happen!?