fiscal periods- ending first friday of everymonth

  • Thread starter Thread starter soltek
  • Start date Start date
S

soltek

Could anyone help me with a formula that would determine our monthl
setup? Every month closes on the first friday of the next month
 
Could anyone help me with a formula that would determine our monthly
setup? Every month closes on the first friday of the next month.
Click to expand...

With a date in A1, this formula should give the first Friday of the following
month:

=DATE(YEAR(A1),MONTH(A1)+1,1)+7-
WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,2))
--ron
 
That works terrific thank you very much. If anyone minds would you know
how to formulate the number of weeks between two dates?
 
I found this formula that gives me the number of weeks between tw
dates.
=TRUNC((B25-A25)/7)&" Weeks "&MOD(B25-A25,7)&" Days"

Can this be adjusted to round up or down to the nearest number o
weeks? i.e. 4 weeks and 4 days = 5 weeks...??

And to go deeper, how could I use this to form totals for each of thes
weeks?? thanks in advance
 
I found this formula that gives me the number of weeks between two
dates.
=TRUNC((B25-A25)/7)&" Weeks "&MOD(B25-A25,7)&" Days"


Can this be adjusted to round up or down to the nearest number of
weeks? i.e. 4 weeks and 4 days = 5 weeks...??
Click to expand...
Use the ROUND formula that McGimpsey posted
And to go deeper, how could I use this to form totals for each of these
weeks?? thanks in advanced
Click to expand...

You could use SUMIF formulas;

=SUMIF(25:25,">="& StartDate,26:26) - SUMIF(25:25,">="& EndDate,26:26)

--ron
 
Back
Top