+ Reply to Thread
Results 1 to 17 of 17

Proper formula for using day numbers

  1. #1
    Registered User
    Join Date
    01-08-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2013
    Posts
    79

    Proper formula for using day numbers

    Hello,

    I am trying to write an IF formula that returns values if the adjacent cell falls into a date range that is measured by the day number that year. For example, If the number is within 20 days of day 45 (less than day 45+10 (55) and greater than day 45-10 (35). I run into a problem when the range falls over the new year, day 365 and day 1. So I have the formula somewhat correct, just not sure on how to do the math for when the range falls over the new year. I imagine it is adding an if greater than 365 or less than 0 then add 365 etc. But can't seems to get it.

    I have attached a sample sheet. Here are the columns:

    A) The day number of the year represented by the formula --> =B3-DATE(YEAR(B3),1,1)+1
    B) The date
    C) Values A
    D) Values B
    E) The formula that only shows/repeats the value of values B/column D if the IF function is true

    You will see the variable numbers highlighted yellow. If you change the day number and the range so it flows over the new year, day 1 or 365, All of the numbers disappear.

    I really hope I am explaining this correctly.

    Thanks so much!
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    05-06-2016
    Location
    Cluj, Romania
    MS-Off Ver
    2007-2010
    Posts
    220

    Re: Proper formula for using day numbers

    Hi,
    Change in your formula (in E3) A3 >>> B3-DATE(YEAR(B3),1,1)+1

  3. #3
    Registered User
    Join Date
    01-08-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2013
    Posts
    79

    Re: Proper formula for using day numbers

    Hello. Thank you for replying.

    I am sorry but I don't understand. Can you explain in a little more detail?

  4. #4
    Forum Contributor
    Join Date
    05-06-2016
    Location
    Cluj, Romania
    MS-Off Ver
    2007-2010
    Posts
    220

    Re: Proper formula for using day numbers

    Sorry, I did not read carefully what you want.
    So, what you want to display when day number is (let's say) 375?
    You want to display the same values when the day number is 10?
    Last edited by TudyBTH; 02-26-2017 at 09:02 PM.

  5. #5
    Registered User
    Join Date
    01-08-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2013
    Posts
    79

    Re: Proper formula for using day numbers

    The day number will never be 375 because there is only 365 days in a year. January 1st is day 1, January 31st is day 31, February 1st is day 32, Today Sunday February 25th is day 57, tomorrow is day 58... and so on. Since the data is only weekdays, the only way I can see getting a range properly is by using the day numbers.

    So for example, I want to return about 2 weeks (14 days) before or after a specific same date of date each year. If I used actual month and day in the formula, a day like February 23rd wouldn't even show up some years because it would land on a weekend. Maybe there is a way to do it that way, but with my knowledge of the program I don't see it.

  6. #6
    Registered User
    Join Date
    01-08-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2013
    Posts
    79

    Re: Proper formula for using day numbers

    So when I use the formula in the sample sheet for today, day 57, and set a range of +/-10, it would work because it would look for day numbers between 47 & 67. It is when I would use day 5, and 5 of the days in the range would fall on the previous year, day 360-365. Or choose day 360 and the range falls on the next year. These dates are required but since the day numbers aren't linear it messes up the math.

  7. #7
    Forum Contributor
    Join Date
    05-06-2016
    Location
    Cluj, Romania
    MS-Off Ver
    2007-2010
    Posts
    220

    Re: Proper formula for using day numbers

    I don't understand.
    Your formula do exactly this for as long as the day number does not exceed 365.

  8. #8
    Registered User
    Join Date
    01-08-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2013
    Posts
    79

    Re: Proper formula for using day numbers

    Ok. Well if I step back on this and bring my need down to is core I am looking for the following:

    In the sample list are several years of data in a column. All of the days are weekdays. I am looking for a formula where it will look for a specific day in each of these years along with a specific range of days before and after.

    I cant figure out how to do this. I thought by using the day numbers it would be easier since the date I specify may not even show up in previous years.

  9. #9
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Proper formula for using day numbers

    Put the date in G5
    Put half the range in H5
    Put: =G5-H5 in K5, =G+H5 in K6
    Column E
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Ben Van Johnson

  10. #10
    Registered User
    Join Date
    01-08-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2013
    Posts
    79

    Re: Proper formula for using day numbers

    Thank you Ben. That brought back only the days in 2016. I am trying to get it to bring back the same range for any of the years the data includes.

  11. #11
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Proper formula for using day numbers

    I don't understand. If the put 11/23/2004 in G5 with a range of ±60, then the dates from 9/24/2004 - 1/21/2005 are highlighted (83 cells/rows) and column E calculations shown. What did you try that didn't work?

  12. #12
    Registered User
    Join Date
    01-08-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2013
    Posts
    79

    Re: Proper formula for using day numbers

    If I take your date above of 11/23 (NO YEAR) with a range of ±60. I want it to work for ALL years in the date range. 2004-2005, 2005-2006, and so on. Not limited to a specific year. This is why I was trying to do it with day numbers as they exclude the year.

  13. #13
    Forum Contributor
    Join Date
    05-06-2016
    Location
    Cluj, Romania
    MS-Off Ver
    2007-2010
    Posts
    220

    Re: Proper formula for using day numbers

    Hi,

    I can't Sent replay
    Attached Files Attached Files

  14. #14
    Forum Contributor
    Join Date
    05-06-2016
    Location
    Cluj, Romania
    MS-Off Ver
    2007-2010
    Posts
    220

    Re: Proper formula for using day numbers


  15. #15
    Registered User
    Join Date
    01-08-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2013
    Posts
    79

    Re: Proper formula for using day numbers

    Wow. That did it. The way you have done so FAR beyond my skill level and comprehension of the program. Thank you so very much for taking the time.

  16. #16
    Registered User
    Join Date
    01-08-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2013
    Posts
    79

    Re: Proper formula for using day numbers

    Actually. I tried changing the day to the higher numbers. Ex. 364 or 359. It seems to not work correctly.

  17. #17
    Forum Contributor
    Join Date
    05-06-2016
    Location
    Cluj, Romania
    MS-Off Ver
    2007-2010
    Posts
    220

    Re: Proper formula for using day numbers

    It makes no sense to introduce a range so large.
    The formula is designed to operate for one year, so you must limit the value range to 365/2 and day value to 365.
    Use Data validation for doing this

+ 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] Proper Phone Numbers Based on Country Code
    By Mohanmoni in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-24-2014, 03:35 PM
  2. Today plus 30 days is showing numbers instead of a proper date
    By enode in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-19-2014, 02:11 PM
  3. [SOLVED] Proper Conversion to numbers
    By nick_0 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-24-2014, 10:46 AM
  4. Replies: 17
    Last Post: 03-26-2014, 12:57 AM
  5. [SOLVED] PROPER Formula
    By JennJenn in forum Excel General
    Replies: 2
    Last Post: 05-11-2012, 07:30 PM
  6. Replies: 2
    Last Post: 01-03-2006, 05:42 PM
  7. Proper way to convert numbers to text
    By Phil in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-20-2005, 06:05 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