+ Reply to Thread
Results 1 to 2 of 2

Cell reference must work with formula output, not formula itself

  1. #1
    Registered User
    Join Date
    03-06-2008
    Posts
    3

    Cell reference must work with formula output, not formula itself

    In a timesheet, I want the result of a formula to depend on the value of a referenced cell, not the formula itself. To clean up my timesheet, I want Excel to leave the cell "hours/day" blank if that day is either Saturday or Sunday.

    Column B has the date in format dd/mm/yyyy
    Column A shows the day of the week (in Dutch) Monday, Tuesday, etc. (abbreviated).
    Column C has the time I started working,
    Column D shows the time I finished working.
    Column E has the amount of time I took for lunch.

    Column F has this formula:
    =IF(A4="za";"";IF(A4="zo";"";((D4-C4-E4)*24)))

    However, even though A4 displays 'za' (the Dutch equivalent for 'Sat') it still does not display a blank cell. I suspect the reason is because my formula refers to the relative value of cell A4, not the absolute value (the result of the formula in A4, rather than the formula itself).

    How can I make sure that for Saturdays and Sundays the fields are left blank?
    Attached Files Attached Files

  2. #2
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Cell reference must work with formula output, not formula itself

    You have to use Text function.

    Try something like this.

    =if(or(text(a4;"ddd")="zo";text(a4;"ddd")="za");"";(d4-c4-e4)*24)))

    ddd format of a date, gives you "Sat" if date is Saturday like 18/08/2012 for example...
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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