Can someone test this IF function for me, I'm not sure that it works.

[dd]

Following on from the post titled "How do I count the number of even weeks
in the current month."
I am taking the number of even weeks per month and assigning payments
against each even week in the month.

Since there are a maximum of three even weeks per month:
For the first even week I assign the maximum value I will receive for that
month.
If the middle of the month has not been reached I assign the remaining value
of £100
If it is none of the above (after the middle of the month) then I assign the
value of £0

I have the following
=IF(TRUNC(((A1-DATE(YEAR(A1),1,0))+6)/7)=A25,A28*100,IF(ROUND(B25-TRUNC(((A1-DATE(YEAR(A1),1,0))+6)/7)/2,-1)=1,100,0))

Broken down:
If the present week number (A1) = the start of month week number (A25)
Then multiply the number of even weeks in this month X £100

If the present week number (A1) divided by two = 1.5
Then the value is £100

Or else the Value is £0

Checking it the start of this month is week 4
The end is week 8

When I try changing my cell values this celldoes not update. I remain at "0"

 

[Niek Otten]

Just a few questions:

What does B25 have to do with this?
Where do you test for 1.5?
Can't you use the WEEKNUM() function?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel



"dd" <dd.dd> wrote in message | Following on from the post titled "How do I count the number of even weeks
| in the current month."
| I am taking the number of even weeks per month and assigning payments
| against each even week in the month.
|
| Since there are a maximum of three even weeks per month:
| For the first even week I assign the maximum value I will receive for that
| month.
| If the middle of the month has not been reached I assign the remaining value
| of £100
| If it is none of the above (after the middle of the month) then I assign the
| value of £0
|
| I have the following
| =IF(TRUNC(((A1-DATE(YEAR(A1),1,0))+6)/7)=A25,A28*100,IF(ROUND(B25-TRUNC(((A1-DATE(YEAR(A1),1,0))+6)/7)/2,-1)=1,100,0))
|
| Broken down:
| If the present week number (A1) = the start of month week number (A25)
| Then multiply the number of even weeks in this month X £100
|
| If the present week number (A1) divided by two = 1.5
| Then the value is £100
|
| Or else the Value is £0
|
| Checking it the start of this month is week 4
| The end is week 8
|
| When I try changing my cell values this celldoes not update. I remain at "0"
|
|
|
|

 

[dd]

Nick,

I've answered your questions inline, below.

Just a few questions:

What does B25 have to do with this?
B25 is the cell containing the Today() Value

Where do you test for 1.5?
I took 1.5 to be a maximum figure, for 3 events per month. Since the process
tells me how much is remaining to be paid for the month, I have taken the
start of the month ans the maximum figure (nr of payments x value), The
second payment as the minimum (1 x value) and the end of the month as 0.

If I could work out how to tell it that the first payment would be received
on the Tuesday of the first, even, week number this would be better.

Can't you use the WEEKNUM() function?
I don't see why not?

--
Regards
Dylan

"dd" <dd.dd> wrote in message
| Following on from the post titled "How do I count the number of even weeks
| in the current month."
| I am taking the number of even weeks per month and assigning payments
| against each even week in the month.
|
| Since there are a maximum of three even weeks per month:
| For the first even week I assign the maximum value I will receive for that
| month.
| If the middle of the month has not been reached I assign the remaining
value
| of £100
| If it is none of the above (after the middle of the month) then I assign
the
| value of £0
|
| I have the following
|
=IF(TRUNC(((A1-DATE(YEAR(A1),1,0))+6)/7)=A25,A28*100,IF(ROUND(B25-TRUNC(((A1-DATE(YEAR(A1),1,0))+6)/7)/2,-1)=1,100,0))
|
| Broken down:
| If the present week number (A1) = the start of month week number (A25)
| Then multiply the number of even weeks in this month X £100
|
| If the present week number (A1) divided by two = 1.5
| Then the value is £100
|
| Or else the Value is £0
|
| Checking it the start of this month is week 4
| The end is week 8
|
| When I try changing my cell values this celldoes not update. I remain at
"0"
|
|
|
|

 

[dd]

Sorry,

I checked myself and A25 = the Start weeknumber in the current month
and B25 = the end weeknumber in the month.
=TRUNC(((B22-DATE(YEAR(B22),1,0))+6)/7)

A1 is the Current date =Today()

Regards
Dylan

Just a few questions:

What does B25 have to do with this?
Where do you test for 1.5?
Can't you use the WEEKNUM() function?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel



"dd" <dd.dd> wrote in message
| Following on from the post titled "How do I count the number of even weeks
| in the current month."
| I am taking the number of even weeks per month and assigning payments
| against each even week in the month.
|
| Since there are a maximum of three even weeks per month:
| For the first even week I assign the maximum value I will receive for that
| month.
| If the middle of the month has not been reached I assign the remaining
value
| of £100
| If it is none of the above (after the middle of the month) then I assign
the
| value of £0
|
| I have the following
|
=IF(TRUNC(((A1-DATE(YEAR(A1),1,0))+6)/7)=A25,A28*100,IF(ROUND(B25-TRUNC(((A1-DATE(YEAR(A1),1,0))+6)/7)/2,-1)=1,100,0))
|
| Broken down:
| If the present week number (A1) = the start of month week number (A25)
| Then multiply the number of even weeks in this month X £100
|
| If the present week number (A1) divided by two = 1.5
| Then the value is £100
|
| Or else the Value is £0
|
| Checking it the start of this month is week 4
| The end is week 8
|
| When I try changing my cell values this celldoes not update. I remain at
"0"
|
|
|
|