June 2016 – tecivo BLOG

tecivo BLOG

My IT notes, 'How To...' guides, Microsoft, SharePoint, Citrix, AppSense, Apple, technology & gadgets and more!

Month: June 2016

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

 

Excel Tips…Using TIME

Introduction

If you want to do calculations based on cells containing times you need to use the TIME and TIMEVALUE functions.

Add 1 hour to time

You can’t simply use =A1+1 to add an hour to a time stored in A1, you need to define which part of the time value – hh:mm:ss – you want to add one to. You must define the value as TIME then use hh,mm,ss options for the value to add

A B < B function >
1 04:00 05:00 =A1+TIME(1,0,0)
2 04:00 04:30 =A2+TIME(0,30,0)

Show a time range using a cells time as starting point

Use CONCATENATE or & to add free text (“~”) between the time from an existing cell and existing cell +3 hours

A B < B function >
1 04:00 04:00~07:00 =CONCATENATE(TEXT(A1,”hh:mm”),”~”,TEXT(A1+TIME(3,0,0),”hh:mm”))
2 04:00 04:00~07:00 =TEXT(A2,”hh:mm”)&”~”&TEXT(A2+TIME(3,0,0),”hh:mm”)

If time in cell is less than defined time minus one day

If you want to use time from a reference cell in a function you have to use TIMEVALUE
e.g. if time in column A cell is less than 01:00, take date from column B cell and -1 day, else leave same value as column B cell

A B C < C function >
1 12:30 AM 10/06/2016 09/06/2016 =IF(A1>=TIMEVALUE(“01:00 AM”),B1,B1-1)
2 02:00 AM 10/06/2016 10/06/2016 =IF(A2>=TIMEVALUE(“01:00 AM”),B2,B2-1)
3 01:00 AM 10/06/2016 10/06/2016 =IF(A3>=TIMEVALUE(“01:00 AM”),B3,B3-1)

Also see

 

© 2019 tecivo BLOG

Theme by Anders NorenUp ↑