Home

Deduct week numbers in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-673546%22%20slang%3D%22en-US%22%3EDeduct%20week%20numbers%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-673546%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20to%20deduct%20week%20numbers%20in%20Excel%20but%20I%20don't%20know%20the%20formula%20(it%20should%20be%20something%20easy%20but%20I%20don't%20know)%3CBR%20%2F%3EFor%20example%20in%20cell%20A1%20I%20have%2001.2019%20(so%20this%20mean%20week%201%20from%202019)%20and%20I%20want%20to%20have%20in%20cell%20B1%20minus%2052%20week%20numbers%20from%20the%20data%20in%20A1%20(so%20I%20should%20get%2001.2018).%3CBR%20%2F%3EWhat%20should%20be%20the%20formula%20in%20cell%20B1%20%3F%20(Something%20like%20%3DA1-52%3F)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20that%20someone%20can%20help%20me%20with%20this%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%20Ilker%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-673546%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-673606%22%20slang%3D%22en-US%22%3ERe%3A%20Deduct%20week%20numbers%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-673606%22%20slang%3D%22en-US%22%3EYour%20formula%20could%20be%3A%3CBR%20%2F%3E%3DA1-7*52%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-673608%22%20slang%3D%22en-US%22%3ERe%3A%20Deduct%20week%20numbers%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-673608%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%3C%2FP%3E%3CP%3EWhen%20I%20use%20that%20formale%20I%20get%2028-11-1931%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20365px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F117321iA4F55F90E9317D6E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Week.jpg%22%20title%3D%22Week.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EAm%20I%20doing%20something%20wrong%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-673633%22%20slang%3D%22en-US%22%3ERe%3A%20Deduct%20week%20numbers%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-673633%22%20slang%3D%22en-US%22%3EYou%20must%20enter%201%2F1%2F2019%20in%20A1%20and%20format%20it%20as%20%22mm.yyyy%22%20so%20that%20the%20formula%20returns%201%2F2%2F2018.%20If%20you%20want%20it%20to%20return%201%2F1%2F2018%2C%20you%20may%20use%20this%20formula%20instead%3A%3CBR%20%2F%3E%3DEDATE(A1%2C-12)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-673727%22%20slang%3D%22en-US%22%3ERe%3A%20Deduct%20week%20numbers%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-673727%22%20slang%3D%22en-US%22%3E%3CP%3EI%20think%20in%20the%20first%20place%20knowing%20the%20networking%20days%20by%20inserting%20the%20formula%20%3DNetworking%20days(Start%20date%2Cend%20date)%20after%20that%20it%20is%20easy%20to%20know%20the%20week%20day%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-673885%22%20slang%3D%22en-US%22%3ERe%3A%20Deduct%20week%20numbers%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-673885%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%20but%20now%20I%20get%20the%20month%20and%20year%2C%20but%20I%20need%20to%20see%20the%20week%20and%20year%20%3A(%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-673894%22%20slang%3D%22en-US%22%3ERe%3A%20Deduct%20week%20numbers%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-673894%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F342478%22%20target%3D%22_blank%22%3E%40Elias2000%3C%2FA%3E%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20593px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F117332i6C3E758890E54A29%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Netwerkdagen.JPG%22%20title%3D%22Netwerkdagen.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EWhen%20I%20use%20your%20formula%20I%20do%20get%20the%20month%20and%2000%20(I%20don't%20know%20what%20this%20is)%20So%20I%20guess%20the%20formula%20needs%20to%20be%20different%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-676418%22%20slang%3D%22en-US%22%3ERe%3A%20Deduct%20week%20numbers%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-676418%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F348050%22%20target%3D%22_blank%22%3E%40ilker_d%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%20Ilker%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWith%20data%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20538px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F117406i733B03A683D70564%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eassuming%20you%20keep%20Week.Year%20as%20text%2C%20formula%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3D(MOD(INT(A2)%2BB2-1%2C52)%2B1)%20%26amp%3B%22.%22%26amp%3B%20(MOD(A2%2C1)*10000%2BINT(B2%2F52))%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
ilker_d
Occasional Contributor

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

 

Thanks in advance

 

Regards, Ilker

7 Replies
Your formula could be:
=A1-7*52

@Twifoo

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

 

 Week.jpg

Am I doing something wrong?

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)

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

@Twifoo 

 

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

@Elias2000 Netwerkdagen.JPG

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

@ilker_d 

 

Hi Ilker,

 

With data like this

image.png

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