﻿﻿﻿﻿ Excel – Page 2

# Category: Excel(Page 2 of 6)

If you have someone’s name in a cell and want to automatically extract initials, you can use LEFT, MID and FIND.

As always when working with text cells, you can wrap with UPPER, LOWER or PROPER.

A B < B function >
1 Joe Bloggs JB =LEFT(A1)&MID(A1,FIND(” “,A1)+1,1)
2 joe bloggs JB =UPPER(LEFT(A2)&MID(A2,FIND(” “,A2)+1,1))

I also found online  a funtion to manage three names – seems long so I imagine there is an easier way to do it, but it works:

A B < B function >
1 Joe John Bloggs JJB =IF(LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))=0,LEFT(A1,1),IF(LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))=1,LEFT(A1,1)&MID(A1,FIND(” “,A1)+1,1),LEFT(A1,1)&MID(A1,FIND(” “,A1)+1,1)&MID(A1,FIND(” “,A1,FIND(” “,A1)+1)+1,1)))

## Introduction

The ExcelIsFun YouTube Channel is a great resource for learning Microsoft Excel and they’ve recently uploaded a fantastic video that explains how to setup and use PivotTables.

I’ve embedded the video below but I would recommend you visit and bookmark their channel as you’ll find loads of really useful videos and answers to most Excel questions you may have.

## Introduction

If you want to do calculations based on date cells you need to use the DATE, WEEKDAY, YEAR and MONTH functions.

### Calculate second Tuesday of month

`=DATE(year,month,(2*7+1)-WEEKDAY(DATE(year,month,8-DAY(dayoftheweek))))`

dayoftheweek Sunday=1, Monday=2, etc

e.g. second Tuesday of June 2016

A < A function >
1 14/06/2016 =DATE(2016,6,(2*7+1)-WEEKDAY(DATE(2016,6,8-DAY(3))))

### Calculate first Saturday of month using reference cells for year and month values

A B C < C function >
1 2016 06 04/06/2016 =DATE(A1,B1,(1*7+1)-WEEKDAY(DATE(A1,B1,8-DAY(7))))

### Calculate first Saturday of following month using reference cells for year and month values

A B C < C function >
1 2016 06 02/07/2016 =DATE(A1,B1+1,(1*7+1)-WEEKDAY(DATE(A1,B1+1,8-DAY(7))))

When you have a date in cell and in another reference it to add 1, Excel will by default add one day

A B < B function >
1 01/06/2016 02/06/2016 =A1+1

Because the cell number format determines how the cells actual value is presented and not the value of the cell itself, adding +1 to a cell containing simply month and year wouldn’t return following month like you might expect.

A B < B function >
1 June 2016 June 2016 =A1+1

Excel stores cell A1 as 01/06/2016 even though the cell number format applied is mmmm yyyy and therefore A1+1 = 02/06/2016 which is being displayed with same number format rule as June 2016.

A B < B function >
1 June 2016 July 2016 =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))