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 […]

# Date & Time

# Select all cells where formulas are made

*By* rushabhs
*in* Date & Time, Formulas, Tips
No Comments

You are working on sales data and need some analysis, so you enter the data as below and make some formulas. After you enter all your formulas, you realize you had to format all those cells which have a formula, no other cells need to formatted. Let’s go step-by-step. Create the sheet with formulas. Select […]

# Find Day name from Date using =TEXT in Excel

*By* rushabhs
*in* Date & Time, Formulas
No Comments
*Tags:* =text, day from date

You have a list of dates and you need to know what are the days of these dates – Monday, Tuesday, Friday? The formula to be used in =TEXT. Simply enter the formula as below, For the Day name in 3 characters, like Mon, Tue, etc., use this =TEXT ( date cell reference, “DDD“) For […]

# Allow to enter date of only current month using Data Validation

*By* rushabhs
*in* Data Validation, Date & Time
2 Comments

You want to allow the user to be able to enter a date of only the current month, and all other dates should be restricted. Eg.: The month is March in 2015 – You want to allow the user to enter a date only between 1st Mar 2015 and 31st Mar 2015. Data Validation can […]

# Convert minutes to hours

*By* rushabhs
*in* Date & Time, Formulas
No Comments

This can easily be done by using the formula =CONVERT Let’s say you have in a cell 1150 minutes written, and you want to convert it to Hours. Cell A1 has 1150 written, we are writing the =CONVERT formula in cell B1. =CONVERT (A1, “mn”, “hr”) There are 3 parts to the formula Reference, the […]

# Auto generate 1st Date of Current Month

*By* rushabhs
*in* Date & Time, Formulas
No Comments

To generate the 1st date of every month in a particular cell is actually quite simple! Today’s date is 2nd July 2014, and I have 01-07-2014 to appear in cell A1. Thus, during entire month of July, cell A1 will have the date 01-07-2014. In August, it will be 01-08-2014. To get this, simply put […]

# =WEEKDAY. Find the Day Number

*By* rushabhs
*in* Date & Time, Formulas
1 Comment

=WEEKDAY returns a Day No. for a particular date, based on which day the date is falling on. Example, =WEEKDAY (A1, 2), where A1 is a Monday, the output of the formula will be 1. The parameter that you choose is extremely important. Please see below table. PARAMETER NUMBER RETURNED 1 or omitted Numbers […]

# Highlight WEEKENDS or WEEKDAYS using conditional Formatting

*By* rushabhs
*in* Conditional Formatting, Date & Time
1 Comment

To highlight Weekends using conditional formatting, is quite simple. You just need to enter 1 formula! Steps: – Select the entire Date column, without header (Do not select the 1st heading row) Go to Home Tab, and then Conditional Formatting Click on New Rule Select the last option “Use a formula to determine which cells […]

# Convert a Month Number to Month Name

*By* rushabhs
*in* Date & Time, Formulas
10 Comments

How do I convert “3” to “March”???It’s very simple.Let’s say you have the number 3 in cell A1 and you want March in Cell B1. Go to cell B1 and type this formula =TEXT ( DATE(2000,A1,10), “MMM”) You will get it Mar.How did this work? The function TEXT works on dates. So first you will […]

# Want to calculate age from date of birth? A faster and easier way!

*By* rushabhs
*in* Date & Time, Formulas
No Comments

Type your birth date in suppose say cell A1. Use the below formula in any other cell: – =DATEDIF(A1, TODAY(), “Y”)This formula is a secret formula so it won’t appear in the in-built formula list. =TODAY() helps in dynamic update everyday. Use can use “M” to calculate months and “D” to calculate days also!Better, isn’t […]