Subtracting numbers representing Weeknum

[Vassen]

Hello,
I need an excel formula that can do a subtraction with numbers that represent week numbers (1 to 52)
i.e
5 - 44 = 13 (if 1st number is lowest thus returning from 5 to 1 then 52 to 44)
44-5 = -13
52 - 2 = -2
2-52 =2
47-44 = 3
44-47=-3
Thanking you for your help

[Special-K]

Are you sure the last two results are correct ?

When A1 is less than B1 the result is positive (5 - 44 = +13, 2 - 52 = + 2)
but this doesn't match the results for 47 - 44

The opposite is true for when A1 is greater than B1.

[Vassen]

Hi Special-K
Yes if A1 is less than B1 the result is positive.
Yes The opposite is true for when A1 is greater than B1.
Thanks for your help

[Special-K]

So the last two results are incorrect?
It should be

47 - 44 = -3
44 - 47 = 3

Are you sure 3 is the correct result?
This doesn't conform to the other examples.

How do you get 3 ?

[Vassen]

Hi Special -K
Yes you are wright the answers should have been as follows
47-44 = 3
44-47= -3

[Special-K]

Hi Special -K
Yes you are wright the answers should have been as follows
47-44 = 3
44-47= -3

No thats still wrong, you've just duplicated your original description of the problem.
And you've contracted your reply from post #3
"Hi Special-K
Yes if A1 is less than B1 the result is positive. "

That's not what the above says.

When I queried are you sure the answer is 3 I wasnt qeurying the + or - I was querying the value of 3.
It doesnt conform to the other formulas.

In the first two formulas if A is less than B the result can be obtained by

(52 - (A - B)) * -1

This works for the first two formulas where A is less than B but not for the third formula
So I am querying whether the value 3 is correct.
I reckon it should be 49.

[Vassen]

Dear Special -K
Let me re explain my problem:
All numbers represent week numbers. I have an excel file in which column A gives the reel week number that a project has been completed,
and in column B you have the week number that the project should have been completed. When comparing column A to B to determine the number
of weeks late or in advance that the project have been completed , I have to subtracts value in A from that in B. As explained if 44 was in
column A and 47 in column B thus 44-47 = -3 meaning 3 week in advance compared to 47.
Same for number 3 in column A and 47 in column B thus 3-47 = +8 weeks late
I hope that It is more clear now.

[JeteMc]

Building on Special-K's formula, the following works for the values given in post #1:

Please Login or Register  to view this content.

Let us know if you have any questions.

[Vassen]

Hi JeteMc,
Yes it works perfectly. Many thanks

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.