+ Reply to Thread
Results 1 to 3 of 3

How to calculate transport with a few conditions

  1. #1
    Registered User
    Join Date
    02-03-2015
    Location
    Singapore
    MS-Off Ver
    Microsoft Office 365
    Posts
    4

    How to calculate transport with a few conditions

    Hi all,

    I need some help to fix my formula.

    The condition to pay the workers transport fee is:
    If it is weekday, we will only pay if they start work before 7am or after 8pm. I.E. to say if they start work at 7am and end at 6pm, we only pay them one way. If they work 7am -8pm, we will pay them 2 ways.

    if it is weekend, we will pay them 2 ways.

    Currently the formula i wrote is:
    IFERROR(IF(WEEKDAY(T7,2)>5,VLOOKUP($F$2,M:Q,3,FALSE),IF(Y7<=(7/24),VLOOKUP($F$2,M:Q,3,FALSE),"-")),"-")

    Unfortunately, I cannot get it to work for those cases that did not cross 8pm. Apparently my formula is still paying them before 8pm. can anyone advise?

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,175

    Re: How to calculate transport with a few conditions

    Maybe

    IFERROR(IF(WEEKDAY(T7,2)>5,VLOOKUP($F$2,M:Q,3,FALSE),IF(AND(Y7<=(7/24), Z7>= 20/24),VLOOKUP($F$2,M:Q,3,FALSE),"-")),"-")
    where Z7 is the end time cell
    ?
    ChemistB
    My 2¢

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    02-03-2015
    Location
    Singapore
    MS-Off Ver
    Microsoft Office 365
    Posts
    4

    Re: How to calculate transport with a few conditions

    Thanks ChemistB for your input. I adjusted it to :

    IFERROR(IF(WEEKDAY(T7,2)>5,VLOOKUP($F$2,M:Q,3,FALSE),IF(OR(W7<=(7/24),X7>=(20/24)),VLOOKUP($F$2,M:Q,3,FALSE),"-")),"-")

    where w7 is start time, and x7 is end time. I dont think it should be using "AND". so i changed to "OR".

    but even so, it still cannot work =(

+ 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. How to calculate transport cost from distance & weight slab
    By linardni in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-18-2013, 12:02 PM
  2. transport information
    By devilonline in forum Excel General
    Replies: 1
    Last Post: 11-03-2012, 06:14 PM
  3. transport add-in
    By dahlgrenen in forum Excel General
    Replies: 2
    Last Post: 04-25-2012, 09:47 PM
  4. How do i transport select visual cells ?
    By kamiland in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 01-02-2007, 07:07 PM
  5. [SOLVED] intersection naming transport between files
    By BorisS in forum Excel General
    Replies: 0
    Last Post: 06-29-2006, 03:40 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1