30. Extract Integer and Decimal Portion of a Number
To extract Integer portion, one of the below can be used -
=INT(A1)
=TRUNC(A1)
Positive value in A1 - If A1 contains 84.65, then answer would be 84.
Negative value in A1 - If A1 contains -24.39, then answer would be -24.
If you want only +ve value whether value in A1 is -ve or +ve, the formula can have many
variants.
=INT(A1)*SIGN(A1) OR =TRUNC(A1)*SIGN(A1)
=INT(ABS(A1)) OR =TRUNC(ABS(A1))
=ABS(INT(A1)) OR = ABS(TRUNC(A1))
To extract Decimal portion -
=MOD(ABS(A1),1)
=ABS(A1)-INT(ABS(A1))
Positive value in A1 - If A1 contains 84.65, then answer would be 0.65.
Negative value in A1 - If A1 contains -24.39, then answer would be 0.39.
31. First Day of the Month for a Given Date
Suppose you have been given a date say 10/22/14 (MM/DD/YY) and you want to calculate
the first day of the Current Month. Hence, you want to achieve a result of 10/1/
(MM/DD/YY).
The formulas to be used -
=DATE(YEAR(A1),MONTH(A1),1)
=A1-DAY(A1)+
=EOMONTH(A1,-1)+
32. How Many Mondays or any other Day of the Week between 2 Dates
Suppose A1 = 23-Jan-16 and A2 = 10-Nov-16. To find number of Mondays between these
two dates
