+ Reply to Thread
Results 1 to 6 of 6

find formula for (formula)+(*1.5 if colum A has certain word) if not use original formula

  1. #1
    Registered User
    Join Date
    02-15-2018
    Location
    Inverness,Scotland
    MS-Off Ver
    2010
    Posts
    2

    find formula for (formula)+(*1.5 if colum A has certain word) if not use original formula

    i have two times of day (start of shift and end of shift) in 2 colums, then the next colum is the difference between the two times. for example
    B3 14:00 C3 16:00 D3 2 - using the formula (c3-b3)*24 in D3

    I Want D3 to times by 1.5 if A3 has 'sat' or 'sun' in the collum (colum A will have a day either mon-sun in it) but i want to remain the normal number of hours if it is a week day in A3

    Any suggestions? the two formulas i have tried to use have been wrong as i cant seem to add 2 words to the search and i dont know how to keep with original formula working out the difference in hours between c3 and b3

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,872

    Re: find formula for (formula)+(*1.5 if colum A has certain word) if not use original form

    Hello and welcome to the forum.

    Attach a sample workbook.

    Make sure there is just enough data to demonstrate your need.

    Make sure your desired results are shown, mock them up manually if necessary.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,872

    Re: find formula for (formula)+(*1.5 if colum A has certain word) if not use original form

    If A3 is a text value and not an actual date that is formatted to show the day of the week, try this in D3:

    =(C3-B3)*24*IF(LEFT(A3)="S",1.5,1)

    If this doesn't work, we'll probably need to see a small representative sample of your data.

  4. #4
    Forum Contributor
    Join Date
    07-06-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2016
    Posts
    192

    Re: find formula for (formula)+(*1.5 if colum A has certain word) if not use original form

    Just like 63falcondude said, if is not an actual date but text value in column A, you can use his method or using the OR formula.

    Example like
    =IF(OR(A3="sat",A3="sun"),(C3-B3)*24*1.5,(C3-B3)*24)

  5. #5
    Registered User
    Join Date
    02-15-2018
    Location
    Inverness,Scotland
    MS-Off Ver
    2010
    Posts
    2

    Re: find formula for (formula)+(*1.5 if colum A has certain word) if not use original form

    Thanks!! works perfectly.

  6. #6
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,872

    Re: find formula for (formula)+(*1.5 if colum A has certain word) if not use original form

    You're welcome. Thanks for the rep!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Worksheet Change? Use a formula cell as input and then revert back to original formula...
    By trickyricky in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-01-2016, 11:39 PM
  2. Formula Help matchin colum a with colum b to display colum c
    By dbe82 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-08-2013, 10:11 PM
  3. Copying formula to new worksheet: how to stop formula linking to original source?
    By test-card girl in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-30-2007, 07:32 AM
  4. IF formula question:edit the fist half of the original formula
    By impress in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-13-2006, 08:35 PM
  5. [SOLVED] Can I Find one word in a formula
    By Rod (Brazil) in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  6. Can I Find one word in a formula
    By Rod (Brazil) in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM
  7. Can I Find one word in a formula
    By Rod (Brazil) in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-19-2005, 08:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts