Dates Formatting

[Cuorepazzu]

Dear

I have the delivery dates and would like to convert them to departure dates.
This means that we calculate 7 days transport for export.
Now the question is:
Is it possible to filter the delivery dates by customer number so that it automatically calculates -7 days?
(The table is created with Power Query)

Thank you very much.

[#2]

@Cuorepazzu Perhaps something like in the attached file?

[#3]

Hi Riny

Yes, exactly. Thank you!
Is there also the option that only the customer number e.g. 12849 and 10096 with -7 days are displayed?

Thanks for your help!

[#4]

@Cuorepazzu Of course, you can filter the others out, but you probably want to do it in a dynamic way. Can you upload (or share a file via Onedrive or similar) with a more realistic example and more than just 3 rows?

[#5]

Please see the link:

The sheet ,,Terminaufträge'' is an input from the connection to our system

Thank you in advance!

[#6]

@Cuorepazzu Well, that file hung-up Excel on my Mac. Thank you!

[#7]

@Riny_van_Eekelen

sorry to hear that! i created it on windows and tried it on my mac today as well. it's not because of the sharelink

[#8]

@Cuorepazzu @Riny_van_Eekelen 

Here is macro-free file with Power Query taken data from the same file in another sheet.

Why don't make calculations in Power Query?

[#9 · Accepted solution]

In Power Query, you can filter delivery dates by customer number and automatically calculate departure dates by subtracting 7 days. Begin by loading your delivery date data into Power Query. Then, create a custom column that filters based on the customer number and subtracts 7 days from the delivery date. To do this,

use the "Add Custom Column" option in Power Query, and write a formula like: = Table.AddColumn(#"PreviousStep", "Departure Date", each if [Customer Number] = YourCustomerNumber then Date.AddDays([Delivery Date], -7) else [Delivery Date]). Replace YourCustomerNumber with the specific customer number for which you want to calculate departure dates.

[#10]

Made my day! Thanks!