Excel – Page 2

Category: Excel (Page 2 of 6)

Excel Tips…Using LEFT, MID and FIND to return initials from a name

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)))

 

Excel Tips…Overview of PivotTables

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.

ExcelIsFun PivotTables & Slicers Made Easy!

 

Excel Tips…Using DATE

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))))

Adding days, months, years

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.
To add one month use

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

To add one year use

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

Also see

 

« Older posts Newer posts »

© 2021

Theme by Anders NorénUp ↑