Complicated Date Calculation needed

[magmike]

I am not very experienced with the date functions and formulas and therefore cannot think of a way to reach the result that I want. I have a cell, let's say A1 that I always want to update on its own based on the date with a twist.

I want it to display what day of the month it is, but only counting weekdays of our fiscal month which is from the 22nd to the 21st of every month. So today, for example, is the 21st weekday of our September, which began on August 22.

Can anybody figure this riddle out?

Thanks in advance for your help,
magmike

 

[isabelle]

hi magmike,

assuming that dates are in the range A1: A31

=21+A1+SUMPRODUCT((WEEKDAY(A1:A31)=7)+(WEEKDAY(A1:A31)=1))-2

isabelle

Le 2013-09-19 22:01, magmike a écrit :

I am not very experienced with the date functions and formulas and therefore cannot think

of a way to reach the result that I want. I have a cell, let's say A1
that I always want to update on its own based on the date with a twist.

I want it to display what day of the month it is, but only counting weekdays

of our fiscal month which is from the 22nd to the 21st of every month.
So today, for example, is the 21st weekday of our September, which began
on August 22.

 

[isabelle]

sorry my first suggestion is wrong,
it is better with this personal function

=Months_Day(A1:A31;21)

Function Months_Day(rng As Range, Number As Integer)
Dim i As Integer, n As Integer
For i = 1 To 31
If n = Number Then Months_Day = rng(i - 1): Exit Function
n = n + 1
If Application.Weekday(rng(i)) = 7 Or Application.Weekday(rng(i)) = 1 Then
n = n - 1
End If
Next

isabelle

Le 2013-09-19 22:46, isabelle a écrit :

 

[magmike]

I am not very experienced with the date functions and formulas and therefore cannot think of a way to reach the result that I want. I have a cell, let's say A1 that I always want to update on its own based on the date with a twist. I want it to display what day of the month it is, but only counting weekdays of our fiscal month which is from the 22nd to the 21st of every month. So today, for example, is the 21st weekday of our September, which began on August 22. Can anybody figure this riddle out? Thanks in advance for your help, magmike

There are no fields with dates in them. I just want A1 to display which working day of th month it is.

How would I call your function in A1 without referencing other fields?

 

[Claus Busch]

Am Thu, 19 Sep 2013 19:01:23 -0700 (PDT) schrieb magmike:

I want it to display what day of the month it is, but only counting weekdays of our fiscal month which is from the 22nd to the 21st of every month. So today, for example, is the 21st weekday of our September, which began on August 22.

try:
=NETWORKDAYS(IF(DAY(TODAY())>22,DATE(YEAR(TODAY()),MONTH(TODAY()),22),DATE(YEAR(TODAY()),MONTH(TODAY())-1,22)),TODAY())


Regards
Claus B.

 

[Claus Busch]

Hi again,

Am Sat, 21 Sep 2013 15:29:09 +0200 schrieb Claus Busch:

=NETWORKDAYS(IF(DAY(TODAY())>22,DATE(YEAR(TODAY()),MONTH(TODAY()),22),DATE(YEAR(TODAY()),MONTH(TODAY())-1,22)),TODAY())

a bit shorter:
=NETWORKDAYS(DATE(YEAR(TODAY()),IF(DAY(TODAY())>22,MONTH(TODAY()),MONTH(TODAY())-1),22),TODAY())


Regards
Claus B.