tecivo BLOG

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

Tag: CONCATENATE (page 1 of 2)

Excel Tips…Using CONCATENATE with dates

Introduction

If you want to include a date value in a cell using CONCATENATE to combine text and other cells, you need to include the TEXT function and include the format of the date you want.

A B C < C function >
1 Stephen 18/12/1946 Stephen’s birthday is 18/12/1946 =CONCATENATE(A1,”‘s birthday is “,TEXT,(B1,”dd/mm/yyyy”))
1 George 14 May 1944 George’s birthday is 14-May-1944 =CONCATENATE(A2,”‘s birthday is “,TEXT,(B2,”dd-mmm-yyyy”))
1 Christopher 30-7-70 Christopher’s birthday is 30 December 1970 =CONCATENATE(A3,”‘s birthday is “,TEXT,(B3,”dd mmmm yyyy”))

 

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

 

Excel Tips…Using VLOOKUP and & to CONCATENATE cells before returning custom text for match/no match found

When using VLOOKUP with data that isn’t made up of unique values you need to combine values to create a unique reference

You can either create additional columns in both data sources and populate with & or CONCATENATE, or you can combine the cell grouping into the VLOOKUP function.

Example – combine Sheet1 columns A and B  to create a unique reference, then use VLOOKUP to search for same in Sheet2 (where an additional column has been created and populated with & or CONCATENATE as appropriate)

=IF(ISNA(VLOOKUP(A1&B1,Sheet2!$A:$A,1,FALSE)),"not found","found")

Sheet1

A B C < C function >
1 Ford Escort found =IF(ISNA(VLOOKUP(A1&B1,Sheet2!$A:$A,1,FALSE)),”not found”,”found”)
2 Lotus Elise found =IF(ISNA(VLOOKUP(A2&B2,Sheet2!$A:$A,1,FALSE)),”not found”,”found”)
3 BMW 330 found =IF(ISNA(VLOOKUP(A3&B3,Sheet2!$A:$A,1,FALSE)),”not found”,”found”)
4 Audi not found =IF(ISNA(VLOOKUP(A4&B4,Sheet2!$A:$A,1,FALSE)),”not found”,”found”)
5 Mini Cooper found =IF(ISNA(VLOOKUP(A5&B5,Sheet2!$A:$A,1,FALSE)),”not found”,”found”)
6 Ford Focus not found =IF(ISNA(VLOOKUP(A6&B6,Sheet2!$A:$A,1,FALSE)),”not found”,”found”)

Sheet2

A B C D E < A function >
1 FordEscort Ford US Escort Red =B1&D1
2 LotusElise Lotus UK Elise Silver =B2&D2
3 BMW330 BMW Germany 330 Blue =B3&D3
4 VWGolf VW Germany Golf Black =B4&D4
5 FordMondeo Ford US Mondeo Silver =B5&D5
6 MiniCooper Mini Cooper =B6&D6

Also see

 

Older posts

© 2017 tecivo BLOG

Theme by Anders NorenUp ↑