Calendar Year in Excel 2010

Calendar Year in Excel 2010

=>Select 31 columns and wanted rows => Paste Formula below with Ctrl +Enter

=DATE(YEAR(B4),MONTH(B4),1)+{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31}-1

B4 is the source of month, example: January-2014. You can change to any month as you want.

Result:

There are formatting in the table. For example, Saturday and Sunday. Please do  as below

=>Select whole month => Conditional Formatting => New Rules => See below formula

=WEEKDAY(DATE(YEAR($B$6),MONTH($B$6),DAY(B$6)),2)>5

This is very important trick. B6 is the first day of the month. Please use ($) only one in DAY.

Calendar Month in Excel 2010

Select 5 rows and 7 columns => Paste formula below with Ctrl + Enter

=DATE(YEAR(B2),MONTH(B2),1)-(WEEKDAY(DATE(YEAR(B2),MONTH(B2),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1

Result:

and then formatting the last month.

=> Select whole calendar => Conditional Formatting =>

LEN Function in Excel 2010

LEN always counts each character, whether single-byte or double-byte, as 1, no matter what the default language setting is.

Syntax:

·         LEN(Text)

Example : =LEN(C2) = 18

	A	B	C
1		Count Text	Text
2	LEN	18	I want to go home.
=LEN(C2)

TIME Function in Excel 2010

The decimal number returned by TIME is a value ranging from 0 (zero) to 0.99999999, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.).

Syntax:

                =TIME(hour, minute, second)

	TIME Function
	A	B	C
1	Serial_Number	3/20/2014 15:42	=NOW()
2	=HOUR(B10)	15
3	=MINUTE(B1)	42
4	=SECOND(B1)	21
5	=TIME(B2,B3,B4)	3:42 PM

Dropdown List with Multi-Criteria in Excel

Drop Dow List with Multi-Criteria in Excel
There is no easy way to do this, but if you know this, it will be easier for you. Below is the list of provinces in Cambodia and the Operational Districts (OD).

You want to select a province, and then OD name can be selected like below, don't you?

There are many steps as below:

Step 1: Select all provinces => Naming cells (2) =>

Step 2:  Naming all ODs /Criteria​ by different short name.

Step 3: Create table for dropdown List in new worksheet.

Step 4: Naming the formula

·         => Formulas => Define Name =OD => using the follow formula.

=IF(List!C5=OD!$B$1,BMC,IF(List!C5=OD!$C$1,BTB,IF(List!C5=OD!$D$1,KgCham,IF(List!C5=OD!$E$1,KgChhnang,IF(List!C5=OD!$F$1,KgSpeu,IF(List!C5=OD!$G$1,KgThom,IF(List!C5=OD!$H$1,KPT,IF(List!C5=OD!$I$1,Kandal,IF(List!C5=OD!$J$1,KK,IF(List!C5=OD!$K$1,KEP,IF(List!C5=OD!$L$1,Kratie,IF(List!C5=OD!$M$1,MDK,IF(List!C5=OD!$N$1,OMC,IF(List!C5=OD!$O$1,Pailin,IF(List!C5=OD!$P$1,PP,IF(List!C5=OD!$Q$1,PVH,IF(List!C5=OD!$R$1,PV,IF(List!C5=OD!$S$1,PST,IF(List!C5=OD!$T$1,RTK,IF(List!C5=OD!$U$1,SR,IF(List!C5=OD!$V$1,SHV,IF(List!C5=OD!$W$1,ST,IF(List!C5=OD!$X$1,SV,IF(List!C5=OD!$Y$1,Takeo)))))))))))))))))))))))) 

Step 5: Data Validation for Province column

·         Select Cells (under Provinces )=> Data => Data Validation => Data Validation => Setting => Data Validation =List => Source = Province

·         Select a cell (under ODs )=> Data => Data Validation => Data Validation => Setting => Data Validation =List => Source =OD

Note: please select only a cell under ODs (D5) for data validation, otherwise, it will not work well. Then you can copy (D5) and paste to other below cells.

=> Download Exercise

Conditional Formatting based on Another Cell

You can use Conditional format based on another cell containing part of text or value.

a.      Conditional Format based on Value

If we want to highlight the lower or higher number, we can use the formula below.

Select Cells => Home=> Conditional Formatting => New Rule… =>

=> Format…=> select format you like => OK

If the column B with number and text and we want to highlight value number, we can use formula below:

Formula: ISNUMBER($B2)

b.      Conditional Format based on Text

The conditional formatting will apply only on text.

·         Select Cells => Home=> Conditional Formatting => New Rule… =>

Formula: ISTEXT($B2)

c.      Conditional Format based on Specific Text

If you want to highlight cells based on specific text, you need to use the formula below:

Formula: =ISNUMBER(SEARCH("Yes",$B2))=TRUE

If you have any update or other found formula, please share or comment below.