• 461K Members
• 5,076 Online
• 559K Conversations

## Deduct week numbers in Excel

Occasional Contributor

# Deduct week numbers in Excel

I want to deduct week numbers in Excel but I don't know the formula (it should be something easy but I don't know)
For example in cell A1 I have 01.2019 (so this mean week 1 from 2019) and I want to have in cell B1 minus 52 week numbers from the data in A1 (so I should get 01.2018).
What should be the formula in cell B1 ? (Something like =A1-52?)

I hope that someone can help me with this

Regards, Ilker

7 Replies

=A1-7*52

# Re: Deduct week numbers in Excel

@Twifoo

When I use that formale I get 28-11-1931:

Am I doing something wrong?

# Re: Deduct week numbers in Excel

You must enter 1/1/2019 in A1 and format it as "mm.yyyy" so that the formula returns 1/2/2018. If you want it to return 1/1/2018, you may use this formula instead:
=EDATE(A1,-12)

# Re: Deduct week numbers in Excel

I think in the first place knowing the networking days by inserting the formula =Networking days(Start date,end date) after that it is easy to know the week day

# Re: Deduct week numbers in Excel

Yes but now I get the month and year, but I need to see the week and year :(

# Re: Deduct week numbers in Excel

When I use your formula I do get the month and 00 (I don't know what this is) So I guess the formula needs to be different

# Re: Deduct week numbers in Excel

Hi Ilker,

With data like this

assuming you keep Week.Year as text, formula could be

`=(MOD(INT(A2)+B2-1,52)+1) &"."& (MOD(A2,1)*10000+INT(B2/52))`
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies