Excel: convert degrees minutes seconds to decimalhttps://joellipman.com/articles/microsoft/excel/excel-convert-degrees-minutes-seconds-to-decimal.html

06 November 2014 work You could merge all the statements into one but for simplicity's sake I'm putting each step here: In column C put the formula: =MID(B1, 1, FIND("°", B1)-1) In column D put the formula: =MID(B1,FIND("°",B1)+1, FIND("′",B1)- FIND("°",B1) - 1) In...

• Type: Article
• Author: Joel Lipman
• Category: Excel
• Language: en-GB

Excel: Find values in one column that are not in anotherhttps://joellipman.com/articles/microsoft/excel/excel-find-values-in-one-column-that-are-not-in-another.html

06 March 2014 the index of column A in which the B2 value was found. -- Returns #N/A for a value which is NOT in column A. Drag the formula in B2 down all the list as per the following screenshot. A value equal to "#N/A" means the value was NOT found in column A....

• Type: Article
• Author: Joel Lipman
• Category: Excel
• Language: *

Excel - Check a column for values found in another columnhttps://joellipman.com/articles/microsoft/excel/excel-check-a-column-for-values-found-in-another-column.html

14 February 2019 shelf allow unity press board wreck shake decay Sheet2 contains the following:: A --------------- warm noise term wreck The formula to paste in column B of Sheet1 is as follows: =SUMPRODUCT(--ISNUMBER(SEARCH(Sheet2!$A$1:$A$4,Sheet1!A1)))>0 Where:...

• Type: Article
• Author: Joel Lipman
• Category: Excel
• Language: en-GB

Convert Decimal (Person Days) to Time in Excelhttps://joellipman.com/articles/microsoft/excel/convert-decimal-person-days-to-time-in-excel.html

13 February 2013 days (a person day being 7 hours and 24 minutes or 26640 seconds). How? The answer was always going to be a convoluted formula just to convert it into minutes and then format the resulting value into a custom format of [h]:mm:ss -- Aim / Objective:...

• Type: Article
• Author: Joel Lipman
• Category: Excel
• Language: en-GB

ZohoCRM: ZDK Client Script to retrieve Contact Emailhttps://joellipman.com/articles/crm/zoho/zohocrm-zdk-client-script-to-retrieve-contact-email.html

20 July 2022 the client wants the email to be prefilled in the field as soon as a contact is selected. How? This needs to behave like a formula field which changes and displays before saving the record. At time of print however, I can't get a formula field to...

• Type: Article
• Author: Joel Lipman
• Category: Zoho
• Language: *

MySQL parameters in Excel 2007 PivotTableshttps://joellipman.com/articles/database/mysql/mysql-parameters-in-excel-2007-pivottables.html

24 August 2011 "Report Filter" section Alternative option... still in progress... Create two cells with the default from and to dates: The formula for the first date of this week was =DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())-WEEKDAY(TODAY())+2) Note the +2 is...

• Type: Article
• Author: Joel Lipman
• Category: MySQL
• Language: en-GB

MS Excel: Convert a text to a numberhttps://joellipman.com/articles/microsoft/excel/ms-excel-convert-a-text-to-a-number.html

15 November 2013 which includes the currency symbol as per the following image: I create another column with the symbol omitted using the formula: =SUBSTITUTE(B3, "₱", "") Which leaves the value as a literal string... Now the tricky part, how to copy into a column just...

• Type: Article
• Author: Joel Lipman
• Category: Excel
• Language: en-GB

Joel's Reference to MidJourney v4 Promptshttps://joellipman.com/graphic-design/joel-s-guide-to-midjourney-prompts.html

11 February 2023 --v 4 nature: from below, side view, golden hour, National Geographic, on Agfa Vista --ar 16:9 Maximize: This is a formula which returns pretty realistic images and references as demo'd in Maximize's Youtube video (see sources below): Type of...

• Type: Article
• Author: Joel Lipman
• Category: Graphic Design
• Language: *

Zoho Deluge: Get the Week Number if Week starts on a Mondayhttps://joellipman.com/articles/crm/zoho/zoho-deluge-get-the-week-number-if-week-starts-on-a-monday.html

06 July 2023 yields: 26 // // Monday, 03-Jul-2023 1 27 // yields: 27 // // Sunday, 09-Jul-2023 7 28 // yields: 27 Originally, I had this formula which seemed to do the same thing but unnecessarily moved the pointer to the start of the week. It could also error...

• Type: Article
• Author: Joel Lipman
• Category: Zoho
• Language: *

ZohoCRM Client Script: On Change of Dropdown: Subform Rewrite: REST Functionhttps://joellipman.com/articles/crm/zoho/zohocrm-client-script-on-change-of-dropdown-subform-rewrite-rest-function.html

01 April 2025 new set of line items a_NewSuformRows.push(o_NewLineItem); } } } // determine deposit amount // get grand total (note that formula for grand total excludes line items which were optional) v_QuoteGrandTotal = 0.00; r_QuoteDetails =...

• Type: Article
• Author: Joel Lipman
• Category: Zoho
• Language: *