Pivot table calculated colum

[mejiaks]

Hi

I have a table that looks more or less like this:
Reference Orde BillTo Date
1 1 C1 01/11/16
1 2 C2 02/11/16
1 3 C3 10/11/16
2 4 C4 02/11/16
2 5 C5 05/11/16
2 6 C6 12/11/16
3 7 C1 03/11/16
3 8 C2 06/11/16

I have made a pivot table that shows the reference as row labels min of date and max of date

Sort of like this

Row Lables Min of Date Max of Date
1 01/11/16 10/11/16
2 02/11/16 12/11/16
3 03/11/16 06/11/16

So far so good
My issue is that i need to add 6 days to the Min of Date and have it to look like this

Row Lables Min of Date Max of Date Exp Date
1 01/11/16 10/11/16 07/11/16
2 02/11/16 12/11/16 08/11/16
3 03/11/16 06/11/16 09/11/16

But i haven't been able to make it work.

Is it me or it can not be done?
=min date cell + 6 deoes not do the trik

Pleade help

 

[SkipVought]

In your source, new column: bill to date + 6

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

Depending on excel version, you can use power query to transform your data. Having it, it's quite simple to create two processes:
process 1): extract unique column 1 with Min of Date, add 6 to Min of Date,
process 2): extract unique column 1 with Max of Date, combine with data from process 1 using link in column 1.

Some tips:
1) before adding 6 to date you may have to convert date to number (at least direct adding failed for me) and convert result back to date,
2) you can delete sheet with [process 1] output table, it will still be stored in file,
3) if you need to link other tables it can be done in consecutive steps in single process.

Power query is really a nice tool, each query is in fact a series of transformations. You can have more than one query in a workbook, they can be joined in various way if you need to relate results. A table or query directly can be the sorce for pivot table. Etc...

combo