Complicated Date Calculation needed

  • Thread starter Thread starter magmike
  • Start date Start date
M

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
 
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
Click to expand...

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
Click to expand...

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.
 
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 :
 
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
Click to expand...

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?
 
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.
Click to expand...

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


Regards
Claus B.
 
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())
Click to expand...

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


Regards
Claus B.
 
Back
Top