Trying to generate actual selling day of current month and current year

  • I am fairly new to Excel and I am trying to create a working formula that will show the current selling day of the month as well as the current selling day for the year. I work at a dealership that is open Monday through Saturday as a regular work week. I am trying to create a formula that will do the following:

    If =TODAY() is 3/10/2016, using the chart below as a reference it would be the 9th selling day (N19) of the month and the 60th selling day of the year (N20).


    [ATTACH=CONFIG]68547[/ATTACH]

    A1 will say “CURRENT DATE:”
    A2 will show “=TODAY()”
    B1 will say “SELLING DAY OF MONTH”
    In B2 I am trying to create a formula to calculate what selling day of the month it is.
    C1 will say “SELLING DAY OF THE YEAR”
    In C2 I am trying to create a formula to calculate what selling day of the year it is.


    In this particular calendar year, we are closed on July 4th and November 24th, as well as each Sunday. So there are only 312 actual selling days for the year

    I have tried using the NETWORKDAYS.INTL, but I am not quite sure of what I am doing. Once I have figured out how to create these two formulas, I can create other simpler formulas that calculate the overall selling pace that we are on for the current month and current year.

    I look forward to any assistance in this area.


    DVSWINDLER

  • Re: Trying to generate actual selling day of current month and current year


    This will pull the day number, using the ranges you indicated...
    =NETWORKDAYS.INTL(EOMONTH(B9,-1)+1,A2,11)


    (working on the year day)

  • Re: Trying to generate actual selling day of current month and current year


    Quote from FDibbins;767121

    This will pull the day number, using the ranges you indicated...
    =NETWORKDAYS.INTL(EOMONTH(B9,-1)+1,A2,11)


    (working on the year day)


    Thank you Ford. I get what you provided, but what I am trying to do is the following:


    1. Based on the current day of the month, let's say that it's March 11, 2016. Based on my reference table, it would be the 10th selling day for the month, and the 61st selling day of the year. I am trying to develop a formula so that when I enter todays' date in one cell, the formula will calculate the selling day of the year and place it in the next cell, as well as calculate the selling day of the year and place it in the next cell over. Is this possible?


    Thanks, dvswindler

  • Re: Trying to generate actual selling day of current month and current year


    You would just need to apply that formula to each date you need to test


    (did you look at the pic you uploaed? It is rweally really small)

  • Re: Trying to generate actual selling day of current month and current year


    I got it to work using the following formula and a new table that i created:


    =if(a3=1,networkdays.intl(g11,a2,i11,j11),if(a3=2,networkdays.intl(g12,a2,i12,j12),if(a3=3,networkdays.intl(g13,a2,i13,j13),if(a3=4,networkdays.intl(g14,a2,i14,j14),if(a3=5,networkdays.intl(g15,a2,i15,j15),if(a3=6,networkdays.intl(g16,a2,i16,j16),if(a3=7,networkdays.intl(g17,a2,i17,j17),if(a3=8,networkdays.intl(g18,a2,i18,j18),if(a3=9,networkdays.intl(g19,a2,i19,j19),if(a3=10,networkdays.intl(g20,a2,i20,j20),if(a3=11,networkdays.intl(g21,a2,i21,j21),if(a3=12,networkdays.intl(g22,a2,i22,j22)))))))))))))

  • Re: Trying to generate actual selling day of current month and current year


    Im sure there is a simpler way to do this.


    What do you have in G11:G22 I11:I22 and J11:J22?
    is there some way we can just use the 1st row info and add something to those dates, based on what A3 contains?