I have two fields in a table containing order details, both formatted as standard and containing (M)MYY:
“ConfirmedWEEK”
“DeliveredWEEK”
Suggestions to extract the corresponding date for use in a Pivot Table ?
I have two fields in a table containing order details, both formatted as standard and containing (M)MYY:
“ConfirmedWEEK”
“DeliveredWEEK”
Suggestions to extract the corresponding date for use in a Pivot Table ?
Not sure what that means. So the cells are currently displaying MYY (monthyear)?both formatted as standard and containing (M)MYY:
Theroretically, if you format the cell as date, it will show the dates.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Cells have Standard format, data input as (M)MYY. Example: 115>>5215.
In theory,yes. In practice,no.
okay, this is an exampleHow would I interpret that? Is that Nov 5th? What are the last 4 digits?data input as (M)MYY. Example: 115>>5215.
Not sure how ">>" relates to the format "(M)MYY"
Interpreting...
An example of what the user must input, (M)MYY, in STD formatted column(s)
">>" means From 115 To 5215 (115..215..XX15..5215)
Last edited by torers; 09-25-2015 at 02:47 PM. Reason: Clarifying
Ahhh, (lightbulb I think) so the first 1 or 2 digits are week numbers and the last 2 are the year?
Bulb lighted.
Further clarification?
so if confirmed week is 715 then you want the first date of that week? How do you want your weeks to run, Sunday to Saturday? Monday to Sunday?
Assuming you want Monday's date, with your weeknumber in A1
=("1/1/"&RIGHT(A1,2))-WEEKDAY(("1/1/"&RIGHT(A1,2))+0,2)+((LEFT(A1,LEN(A1)-2)-1)*7)+1
for example, 1115 returns 3/9/2015 which is the monday of week 11 in 2015
Remember to format cell as a date
Note: Change Commas to Semicolons if necessary
Thank you!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks