+ Reply to Thread
Results 1 to 3 of 3

Can I turn a formula into a custom function to shorten it?

  1. #1
    Registered User
    Join Date
    08-13-2012
    Location
    Wallingford, Oxfordshire, UK
    MS-Off Ver
    Excel 2007
    Posts
    8

    Can I turn a formula into a custom function to shorten it?

    I have entered the following into a cell so that it always gives me the date of the Friday of the week after the current week.

    =TODAY()+IF(TEXT(TODAY(),"ddd")="Sat",13,IF(TEXT(TODAY(),"ddd")="Sun",12,IF(TEXT(TODAY(),"ddd")="Mon",11,IF(TEXT(TODAY(),"ddd")="Tue",10,IF(TEXT(TODAY(),"ddd")="Wed",9,IF(TEXT(TODAY(),"ddd")="Thu",8,IF(TEXT(TODAY(),"ddd")="Fri",7,0)))))))

    It is rather long and I read that you can create custom functions.

    I tried entering

    Function NextFriday()

    =TODAY()+IF(TEXT(TODAY(),"ddd")="Sat",13,IF(TEXT(TODAY(),"ddd")="Sun",12,IF(TEXT(TODAY(),"ddd")="Mon",11,IF(TEXT(TODAY(),"ddd")="Tue",10,IF(TEXT(TODAY(),"ddd")="Wed",9,IF(TEXT(TODAY(),"ddd")="Thu",8,IF(TEXT(TODAY(),"ddd")="Fri",7,0)))))))

    End Function

    When I type =NextFriday into a cell (NextFriday appeared in the list of functions which was encouraging!)
    I get #NAME?

    I am very new to all this so can anyone help?

    Thank you

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Can I turn a formula into a custom function to shorten it?

    Try

    =TODAY()+13-MOD(WEEKDAY(TODAY()),7)
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Valued Forum Contributor
    Join Date
    10-29-2012
    Location
    Mojokerto,Indonesia
    MS-Off Ver
    Excel 2007
    Posts
    554

    Re: Can I turn a formula into a custom function to shorten it?

    try this
    =IF(WEEKDAY(TODAY())=6,TODAY()+7,TODAY()+(6-WEEKDAY(TODAY())))

+ 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