Home

Extract a string of variable length and position from a cell

%3CLINGO-SUB%20id%3D%22lingo-sub-548175%22%20slang%3D%22en-US%22%3EExtract%20a%20string%20of%20variable%20length%20and%20position%20from%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-548175%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20value%20in%20one%20cell%3A%26nbsp%3B%201191015.%26nbsp%3B%20This%20is%20a%20date%20in%20Mapics%20(century-year-month-day).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20substring%20this%20to%3A%26nbsp%3B%2010%2F15%2F2019%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-548175%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-548447%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20a%20string%20of%20variable%20length%20and%20position%20from%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-548447%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F338076%22%20target%3D%22_blank%22%3E%40BeckyC%3C%2FA%3E%26nbsp%3B%2C%20how%20to%20convert%20Mapics%20dates%20to%20calendar%20dates%20is%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww-01.ibm.com%2Fsupport%2Fdocview.wss%3Fuid%3Dswg21333308%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww-01.ibm.com%2Fsupport%2Fdocview.wss%3Fuid%3Dswg21333308%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20Mapics%20day%20is%20in%20A1%20it'll%20be%3C%2FP%3E%0A%3CPRE%3E%3DDATE(FLOOR(A1%2F10000%2C1)%20%2B%201900%2CFLOOR(MOD(A1%2C%2010000)%20%2F%20100%2C1)%2CMOD(A1%2C%20100%20))%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
BeckyC
Occasional Visitor

I have a value in one cell:  1191015.  This is a date in Mapics (century-year-month-day).

 

I want to substring this to:  10/15/2019

 

Thanks!

1 Reply

@BeckyC , how to convert Mapics dates to calendar dates is here https://www-01.ibm.com/support/docview.wss?uid=swg21333308

 

If you Mapics day is in A1 it'll be

=DATE(FLOOR(A1/10000,1) + 1900,FLOOR(MOD(A1, 10000) / 100,1),MOD(A1, 100 ))
Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies