Formulas para manipular fechas y números en Excel, Esquemas y mapas conceptuales de Informática

¡Descarga Formulas para manipular fechas y números en Excel y más Esquemas y mapas conceptuales en PDF de Informática solo en Docsity!

Excel 365

Prepared & presented by

Mubarak Ghaith

September 2023

Table of Contents

• 1. SUM of Digits when cell Contains all Numbers
• 2. SUM of Digits when cell Contains Numbers and non Numbers both
• 3. A List is Unique or Not (Whether it has duplicates)
• 4. Count No. of Unique Values
• 5. Count No. of Unique Values Conditionally
• 6. Generate Sequential Weekday names like Sun, Mon, Tue, .....,Sat
• 7. Generate Sequential Month names like Jan, Feb, Mar....Dec
• 8. Find Last Day of the Month
• 9. Number of Days in a Month
• 10. Find First Day of the Month..................................................................................................................
• 11. Add Month to or Subtract Month from a Given Date
• 12. Add Year to or Subtract Year from a Given Date
• 13. Convert a Number to a Month Name
• 14. Convert a Month Name to Number
• 15. Convert a Number to Weekday Name
• 16. Convert a Weekday Name to Number
• 17. Financial Year Formula (e.g. 2015-16 or FY16)
• 18. Converting Date to a Calendar Quarter
• 19. Converting Date to a Indian Financial Year Quarter...................................................................
• 20. Determine Quarter for Fiscal Year
• 21. Calculate Age from Given Birthday
• 22. Convert from dd/mm/yy to mm/dd/yy (DMY to MDY)
• 23. Convert from mm/dd/yy to dd/mm/yy (MDY to DMY)
• 24. Number to Date Format Conversion
• 25. Number to Time Format Conversion.............................................................................................
• 26. Convert Time to Decimal Hours, Minutes and Seconds
• 27. Convert Decimal Hours, Minutes and Seconds to Time
• 28. Generate a Sequence of Dates
• 29. Generate a Sequence of Times
• 30. How to Know if a Year is a Leap Year
• 31. Last Working Day of the Month If a Date is Given
• 32. First Working Day of the Month if a Date is Given
• 33. Determine Number of Working Days in a Year
• 34. Determine Number of Working Days in a Month
• 35. How Many Mondays or any other Day of the Week between 2 Dates
• 36. Find Number of Friday the 13th between Two Given Dates
• 37. Calculate Next Working day if date falls on a Weekend / Holiday
• 38. Calculate Previous Working day if date falls on a Weekend / Holiday
• 39. Date for Nth Day of the Year
• 40. Extract Date and Time from Date Timestamp
• 41. First Working Day of the Year
• 42. Last Working Day of the Year
• 43. Convert from Excel Date (Gregorian Date) to Julian Date
• 44. Convert from Julian Dates to Excel (Gregorian) Dates
• 45. Convert a Number into Years and Months
• 46. Find the Next Week of the Day
• 47. Find the Previous Week of the Day
• 48. Count Cells Starting (or Ending) with a particular String
• 49. Count No. of Cells Having Numbers Only
• 50. Reverse a String
• 51. Reverse Number String
• 52. Reverse a String of Words..................................................................................................................
• 53. Reverse a Range of Cells
• 54. Get First Row Number in a Range
• 55. Get Last Row Number in a Range
• 56. Get First Column Number in a Range
• 57. Get Last Column Number in a Range
• 58. Extract Unique Characters
• 59. Test whether a range contains only numbers............................................................................
• 60. Test whether a range contains only English alphabets
• 61. Count No. of Cells which are containing only Characters
• 62. Number of Characters in a String without considering blanks
• 63. Number of times a character appears in a string......................................................................
• 64. Count Non Numbers in a String
• 65. Count Numbers in a String.................................................................................................................
• 66. Count only Alphabets in a String
• 67. Generate English Alphabets
• 68. Convert English Alphabets to Numbers
• 69. Convert Numbers to English Alphabets
• 70. Extract nth Word from Front
• 71. Extract nth Word from Back
• 72. Extract first two words OR first n words
• 73. Extract last two words OR last n words
• 74. Most Frequently Occurring Value in a Range
• 75. COUNTIF with OR Condition
• 76. SUMIF with OR Condition
• 77. COUNTIF on Filtered List
• 78. SUMIF on Filtered List
• 79. Extract First Name from Full Name
• 80. Extract Last Name from Full Name
• 81. Extract the Initial of Middle Name
• 82. Extract Middle Name from Full Name
• 83. Remove Middle Name in Full Name
• 84. Extract Integer and Decimal Portion of a Number
• 85. Maximum Times a Particular Entry Appears Consecutively
• 86. Get File Name through Formula
• 87. Get Workbook Name through Formula
• 88. Get Sheet Name through Formula
• 89. Get Workbook's Directory from Formula
• 90. Perform Multi Column VLOOKUP
• 91. VLOOKUP from Right to Left
• 92. Case Sensitive VLOOKUP
• 93. Rank within the Groups
• 94. Extract Alphabets from a String
• 95. Remove Alphabets from a String.....................................................................................................
• 96. Extract numbers from string
• 97. Remove numbers from string...........................................................................................................
• 98. Roman Representation of Numbers...............................................................................................
• 99. Sum Bottom N Values in a Range
• 100. Sum Every Nth Row
• 101. Sum Every Even Row
• 102. Sum Every Odd Row
• 103. Sum Top N values in a Range
• 104. We have AVERAGEIF. What about MEDIANIF and MODEIF?
• 105. Calculate Geometric Mean by Ignoring 0 and Negative Values
• 106. Generate GL Codes............................................................................................................................
• 107. Abbreviate Given Names
• 108. Get Column Name for a Column Number
• 109. Get Column Range for a Column Number
• 110. Find the nth Largest Number when there are duplicates
• 111. COUNTIF for non-contiguous range
• 112. Count the Number of Words in a Cell / Range.......................................................................
• 113. Numerology Sum of the Digits aka Sum the Digits till the result is a single digit
• 114. Generate Sequential Numbers and Repeat them..................................................................
• 115. Repeat a Number and Increment and Repeat....
• 116. Generate Non Repeating Random Numbers through Formula
• 117. Extract User Name from an E Mail ID
• 118. Extract Domain Name from an E Mail ID
• 119. Location of First Number in a String
• 120. Location of Last Number in a String
• 121. Find the Value of First Non Blank Cell in a Range
• 122. Find First Numeric Value in a Range
• 123. Find Last Numeric Value in a Range
• 124. Find First non Numeric Value in a Range
• 125. Find Last non Numeric Value in a Range
• 126. Find Last Used Value in a Range
• 127. I have data for many years but I want the sum for only last 12 months
• 128. Generate a Unique List out of Duplicate Entries
• 129. Financial Function - Calculate EMI.............................................................................................
• 130. Financial Function - Calculate Interest Part of an EMI
• 131. Financial Function - Calculate Principal Part of an EMI.....................................................
• 132. Financial Function - Calculate Number of EMIs to Pay Up a Loan
• 133. Financial Function - Calculate Interest Rate...........................................................................
• 134. Financial Function – Calculate Compounded Interest
• 135. Financial Function – Calculate Effective Interest
• 136. Financial Function – Calculate CAGR and AAGR
• 137. Slab Billing – Calculate Income Tax, Electricity (Utility) Bills based on Slabs
• 138. LTRIM and RTRIM through Excel Formulas

1. SUM of Digits when cell Contains all Numbers If you cell contains only numbers like A1:= 7654045, then following formula can be used to find sum of digits =SUM(--MID(A1,SEQUENCE(LEN(A1)),1)) =SUMPRODUCT(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)) =SUM(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)) If A1 is blank, then to handle error, you can enclose above formulas into an IFERROR block. 2. SUM of Digits when cell Contains Numbers and non Numbers both If your cell contains non numbers apart from numbers like A1:= 76$5a4b045%d, then following formulas can be used to find sum of digits =SUM(IFERROR(--MID(A1,SEQUENCE(LEN(A1)),1),0)) =SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(A1,ROW($1:$9),"")))*ROW($1:$9)) =SUM(IFERROR(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),0)) 3. A List is Unique or Not (Whether it has duplicates) Assuming, your list is in A1 to A1000. Use following formula to know if list is unique. =MAX(COUNTIF(A1:A1000,A1:A1000)) If answer is 1, then it is Unique. If answer is more than 1, it is not unique. 4. Count No. of Unique Values Use following formula to count no. of unique values - =IF(COUNTA(A1:A100)=0,0,COUNTA(UNIQUE(FILTER(A1:A100&"",A1:A100<>"")))) =SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&"")) =SUM((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&"")) 5. Count No. of Unique Values Conditionally If you have data like below and you want to find the unique count for Region = “A", then you can use below formula – Mubarak G haith

=IF(COUNTIF(A2:A20,"A")=0,0,COUNTA(UNIQUE(FILTER(B2:B20,A2:A20="A"))))

=SUM(IF(FREQUENCY(IF(A2:A20<>"",IF(A2:A20="A",MATCH(B2:B20,B2:B20,0))),ROW(A

2:A20)-ROW(A2)+1),1))

If you have more number of conditions, the same can be built after A2:A20 = “A".

6. Generate Sequential Weekday names like Sun, Mon, Tue, .....,Sat For 3 characters Weekday name =TEXT(DATE(2017,1,SEQUENCE(7)),"ddd") =TEXT(DATE(2017,1,ROW($1:$7)),"ddd") (Note – Year 2017 has been used as 1-Jan-2017 was Sunday. You can use any year in place of 2017 where 1-Jan was a Sunday like year 2023) For full Weekday name, replace ddd with dddd =TEXT(DATE(2017,1,SEQUENCE(7)),"dddd") =TEXT(DATE(2017,1,ROW($1:$7)),"dddd") You can add a number if you want to show some other Weekday Name. Say, if you want to show 1 = Monday, 2 = Tuesday…….7 = Sunday, just add 1. Hence, formulas would become =TEXT(1+DATE(2017,1,SEQUENCE(7)),"ddd") =TEXT(1+DATE(2017,1,ROW($1:$7)),"ddd") Mubarak G haith

=A1-DAY(A1)+

=EOMONTH(A1,-1)+

=DATE(YEAR(A1),MONTH(A1),1)

11. Add Month to or Subtract Month from a Given Date Very often, you will have business problems where you have to add or subtract month from a given date. One scenario is calculation for EMI Date. Say, you have a date of 10/22/ 21 (MM/DD/YY) in A1 and you want to add number of months which is contained in Cell B1. The formula in this case would be =EDATE(A1,B1) [Secondary formula =DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1)) ] Now, you want to subtract month which is contained in Cell B1. =EDATE(A1,-B1) [Secondary formula =DATE(YEAR(A1),MONTH(A1)-B1,DAY(A1)) ] 12. Add Year to or Subtract Year from a Given Date In many business problems, you might encounter situations where you will need to add or subtract years from a given date. Let's say A1 contains Date and B1 contains numbers of years. If you want to add Years to a given date, formulas would be - =EDATE(A1,12B1) =DATE(YEAR(A1)+B1,MONTH(A1),DAY(A1)) If you want to subtract Years from a given date, formulas would be - =EDATE(A1,-12B1) =DATE(YEAR(A1)-B1,MONTH(A1),DAY(A1)) 13. Convert a Number to a Month Name Use below formula to generate named 3 lettered month like Jan, Feb....Dec Mubarak G haith

=TEXT(A1*30,"mmm") Replace "mmm" with "mmmm" to generate full name of the month like January, February....December in any of the formulas in this post.

14. Convert a Month Name to Number Say Cell A1 contains the string January, February….December (or Jan. Feb…..Dec) and you want to show 1, 2…… =MONTH("1"&A1) The formula would work as long as month names are >=3 characters. Hence, it would work for say Janu or Decem or Apri or Octobe. 15. Convert a Number to Weekday Name Suppose you want to return 1 = Sunday, 2 = Monday…..7 = Saturday =TEXT(DATE(2017,1,A1),"dddd") Note – 2017 has been used in above formula as 1-Jan-2017 was Sunday. You can use any year where 1-Jan was Sunday like year 2023. To show only 3 characters of the Weekday Name, replace dddd with ddd =TEXT(DATE(2017,1,A1),"ddd") You can add a number to A1 if you want to show some other Weekday Name Say, if you want to show 1 = Monday, 2 = Tuesday…….7 = Sunday, just add 1 to A =TEXT(1+DATE(2017,1,A1),"dddd") Say, if you want to show 1 = Friday, 2 = Saturday…….7 = Thursday, just add 5 to A =TEXT(5+DATE(2017,1,A1),"dddd") 16. Convert a Weekday Name to Number Say Cell A1 contains the string Sunday, Monday….Saturday (or Sun, Mon…..Sat) and you want to show 1, 2…..7, then following formula can be used to return the numbers. Sunday will be 1 and Saturday will be 7. =ROUND(SEARCH(LEFT(A1,2),"SuMoTuWeThFrSa")/2,0) =MATCH(LEFT(A1,2),{"Su","Mo","Tu","We","Th","Fr","Sa"},0) If we want to return some other number to weekdays, then formula can be tweaked accordingly. For example, to make Mon = 1 and Sun = 7 Mubarak G haith

20. Determine Quarter for Fiscal Year Few countries follow different quarter other than Q1 from Jan-Mar and Q2 for Apr-Jun. In case of Jan-Mar as Q1, formula is simple (if cell A2 is date) =ROUNDUP(MONTH(A2)/3,0) This will give result as 1, 2, 3 & 4 for the quarters. If you want, you can concatenate "Q" in the formula to show Q1, Q2 etc as below ="Q"&ROUNDUP(MONTH(A2)/3,0) If your financial / fiscal year starts in Apr, then for Jan-Mar, quarter is 4 whereas for Apr to Jun, quarter is 1 and so on. In this case, you can use following formula =CEILING(MONTH(EDATE(A1,-3))/3,1) = ROUNDUP(MONTH(EDATE(A1,-3))/3,0) If your financial / fiscal year starts in Jul, then for Jan-Mar, quarter is 3 whereas for Jul to Sep, quarter is 1 and so on. In this case, you can use following formula =CEILING(MONTH(EDATE(A1,-6))/3,1) = ROUNDUP(MONTH(EDATE(A1,- 6 ))/3,0) If your financial / fiscal year starts in Oct, then for Jan-Mar, quarter is 2 whereas for Oct to Dec, quarter is 1 and so on. In this case, you can use following formula =CEILING(MONTH(EDATE(A1,- 9 ))/3,1) = ROUNDUP(MONTH(EDATE(A1,- 9 ))/3,0) 21. Calculate Age from Given Birthday =DATEDIF(A1,TODAY(),"y")&" Years "&DATEDIF(A1,TODAY(),"ym")&" Months "&DATEDIF(A1,TODAY(),"md")&" Days" 22. Convert from dd/mm/yy to mm/dd/yy (DMY to MDY) Say you have following dates in DMY format 24/8/ 24/8/ 4/08/ 04/08/ And you need to convert them into MDY format, then use the following formula Case1 – if your default date format is MDY Mubarak G haith

=FILTERXML(""&SUBSTITUTE(TEXT(A1,"mm/dd/yyyy"),"/","")&"</t

","//s[2]")&"/"&FILTERXML(""&SUBSTITUTE(TEXT(A1,"mm/dd/yyyy"),"/","</s ")&"","//s[1]")&"/"&FILTERXML(""&SUBSTITUTE(TEXT(A1,"mm/d d/yyyy"),"/","")&"","//s[3]") Case2 – if your default date format is DMY =FILTERXML(""&SUBSTITUTE(TEXT(A1,"dd/mm/yyyy"),"/","")&"</t ","//s[2]")&"/"&FILTERXML(""&SUBSTITUTE(TEXT(A1,"dd/mm/yyyy"),"/","</s ")&"","//s[1]")&"/"&FILTERXML(""&SUBSTITUTE(TEXT(A1,"dd/m m/yyyy"),"/","")&"","//s[3]")