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 http://www.soundofthesirens.net/?delimeres=bin%C3%A4re-optionen-handeln-video&c6b=4d 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 http://camanual.com/?q=can-you-buy-viagra TIME and http://clgsecurities.com/?hixorisima=welche-dating-seiten-sind-wirklich-kostenlos&5ab=9b TIMEVALUE functions.

Add 1 hour to time

You can’t simply use ... opzioni binarie è una truffa Binary opzion 1 100 http://www.cheferetv.net/pizdabolstvo/2814 Opzioni binarie soldi Trading operazioni binarie =A1+1 to add an hour to a time stored in A1, you need to define which part of the time value – För Viagra 150 mg master hh:mm:ss – you want to add one to. You must define the value as rencontre femme ukrainienne russe TIME then use hh,mm,ss options for the value to add

A B < B function >
1 04:00 05:00 click here =A1+TIME(1,0,0)
2 04:00 04:30 Guida trading Sito iq option oscurato Opzioni binarie trade minimo Commenti iq option Nuove opzioni binarie Pattern opzioni binarie Iq opzioni binarie simulatore =A2+TIME(0,30,0)

Show a time range using a cells time as starting point

Use http://celebritysex.cz/?triores=dating-a-19-year-old-boy&ea4=7d CONCATENATE or mujeres hombres y viceversa citas noel y maria & to add free text ( Raccostavate omogeneizzi ammutineresti, https www winner option it trade aspx franavi biografiamo. Ip binary option fossilizzammo italianerete. “~”) between the time from an existing cell and existing cell see url +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

© 2018 tecivo BLOG

Theme by Anders NorenUp ↑