Rounding dates to specific working days of the week
Hi - I need to round order dates to dates in the future based on a fixed delivery schedule, for example:
Order 1 Created 13/07/2016 - round to the next Tues
Order 2 Created 13/07/2016 - round to the next Weds
Order 3 Created 14/07/2016 - round to the next Mon etc etc
Can anyone advise a solution for this?
Many thanks
etc etc?Originally Posted by Sarah C
How are you actually determining which is the next date to 'round' up to?
let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source
If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE
Walking the tightrope between genius and eejit...
Both have the same date yet one gets rounded to Tues while the other gets rounded to Wed.
What is the logic behind this?
I predict this is going to be a nightmare!
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Hey - thanks for the quick response! The delivery dates (so the round up to dates) are all pre-determined at customer level so this will be a fixed field per order.
We could therefore have different orders (which will be for different customers) that will need to round to different dates.
So:
Customer 1 takes delivery Tuesday, order created Monday, round up date to the next Tuesday
Customer 2 takes delivery Weds, order created Sunday, round up date to the next Weds
Customer 3 takes delivery Tuesday, order created Thursday, round up date to the next Tuesday
Hence the etc in the original post, there could be a number of different combinations. I have the weekday numbers for both the order creation date and the customer delivery day but need to calculate the round up date.
Thanks for all your help!!
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
Actually, try this:
Formula:
Please Login or Register to view this content.
Where myOrderDate and myDeliveryWeekday are cell references...
Perfect! Thanks so much for your help, very much appreciated!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks