auto date filler

Patcheen · Jan 27, 2019

ive a workbook that the days never change

in A2 i have 10/09/15 (Saturday) and in A3 i have 09/09/15 (Wednesday)

in A4 i have 12/09/15 (Saturday) and in A4 i have 16/09/15 (Wednesday)

so on and so on

so instead of having to put in the date manually id like a formula to do this for me

can anyone help please

TIA

JLGWhiz · Jan 27, 2019

First:

in A2 i have 10/09/15 (Saturday) and in A3 i have 09/09/15 (Wednesday)

does not appear to be valid dates.
Second: Perhaps if you could give a scenario on how you arrived at the dates you entered on your sheets, we could better understand what you need.

Fluff · Jan 27, 2019

How about

Excel 2013/2016

A

B

1

2

05/09/2015

Saturday

3

09/09/2015

Wednesday

4

12/09/2015

Saturday

5

16/09/2015

Wednesday

6

19/09/2015

Saturday

7

23/09/2015

Wednesday

8

26/09/2015

Saturday

List

Cell Formulas
Range	Formula
A3	=A2+3+MOD(ROW(),2)

sandy666 · Jan 27, 2019

another way with PowerQuery

Code:

[SIZE=1]let
    Source = List.Dates(#date(2015, 09, 09), 365, #duration(1, 0, 0, 0)),
    DayName = Table.AddColumn(Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), "Day Name", each Date.DayOfWeekName([Column1]), type text),
    TypeDate = Table.TransformColumnTypes(Table.RenameColumns(Table.SelectRows(DayName, each ([Day Name] = "Saturday" or [Day Name] = "Wednesday")),{{"Column1", "Dates"}}),{{"Dates", type date}})
in
    TypeDate[/SIZE]

in this case M-code generate 105 dates (Wednesday, Saturday)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Dates[/td][td=bgcolor:#70AD47]Day Name[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

09/09/2015

[/td][td=bgcolor:#E2EFDA]Wednesday[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

12/09/2015