## How can I conditionally format cells to denote past/future/current dates?

I have a church rota Google Sheet. Column A shows the date of each service.

To make it more obvious at a glance, I would like cells in column A to apply formatting so that dates in the past are colored differently. Ideally, the next upcoming date would be highlighted (or at least dates in the current month or something like this).

I am somewhat familiar with conditional formatting but not using it on dates – is this feasible and if so how can I do it? In a perfect world, the entire row would be colored for past weeks but just the cell is sufficient if this is getting tricky.

## Order query() by dates using the format Mon/D/YY (Apr/13/19)

I’m importing data from a sheet that uses the Mon/D/YY date format (Apr/13/19).

When I try to sort the entries chronologically with the following request, they’re instead sorted alphabetically (A is the column with dates). `SELECT A, B, C, D, E ORDER BY A ASC`

The command `TEXT(date, 'YYYY-MM-DD')` allows me to get a date format I can easily sort, but it’d be way easier if I could do it in the first query.

Thank you!

## Google Sheets Conditional Formatting for expiration dates

I need help!

I need to create conditional formatting for expiration dates:

Cells should stay green

Cells should turn yellow when 1 month of exp. date

Cells should turn red once expired

## How to restrict dates in the form widget?

I am attempting an upgrade and I am looking for something that mirrors the functionality of the Date Restrictions module

Basically, I used it to set relative limits,, something like no earlier than two days ago and no later than a week from now. I also had something that restricted selection to exclude weekends, probably a js function somewhere.

I searched and couldn’t find a module with the same functionality, so I thought I’d just attach a js to either a theme or custom module and be done with it. Well, after a while I found out that the widget differs to the browser and lets it decide wether to use HTML5 or a jQuery date picker.

So, if there isn’t a module for that kind of functionality yet, is there a way to do it that can cover both ways the widget is handled?

## Using date range filter between dates in amcharts

I’m using amcharts4 for my data visualization, I’m currently searching a way to filter my graph according to date filter like this: https://www.amcharts.com/docs/v3/tutorials/stock-chart-period-selector-scrollbar/, but I dont know how to apply it in amcharts4 anyhelp? I can’t find it either in the docu of amcharts4

``initChart();  function initChart(){ // Themes begin am4core.useTheme(am4themes_dark); am4core.useTheme(am4themes_animated); // Themes end  // Create chart instance var chart = am4core.create("chartdiv", am4charts.XYChart);  // Add data chart.data = [{   "date": "2012-09-08",   "value": 43 }, {   "date": "2012-09-09",   "value": 40 }, {   "date": "2012-09-10",   "value": 39 }, {   "date": "2012-09-11",   "value": 34 }, {   "date": "2012-09-12",   "value": 29 }, {   "date": "2012-09-13",   "value": 34 }, {   "date": "2012-09-14",   "value": 37 }, {   "date": "2012-09-15",   "value": 42 }, {   "date": "2012-09-16",   "value": 49 }, {   "date": "2012-09-17",   "value": 46 }, {   "date": "2012-09-18",   "value": 47 }, {   "date": "2012-09-19",   "value": 55 }, {   "date": "2012-09-20",   "value": 59 }, {   "date": "2012-09-21",   "value": 58 }, {   "date": "2012-09-22",   "value": 57 }, {   "date": "2012-09-23",   "value": 61 }, {   "date": "2012-09-24",   "value": 59 }, {   "date": "2012-09-25",   "value": 67 }, {   "date": "2012-09-26",   "value": 65 }, {   "date": "2012-09-27",   "value": 61 }, {   "date": "2012-09-28",   "value": 66 }, {   "date": "2012-09-29",   "value": 69 }, {   "date": "2012-09-30",   "value": 71 }, {   "date": "2012-10-01",   "value": 67 }, {   "date": "2012-10-02",   "value": 63 }, {   "date": "2012-10-03",   "value": 46 }, {   "date": "2012-10-04",   "value": 32 }, {   "date": "2012-10-05",   "value": 21 }, {   "date": "2012-10-06",   "value": 18 }, {   "date": "2012-10-07",   "value": 21 }, {   "date": "2012-10-08",   "value": 28 }, {   "date": "2012-10-09",   "value": 27 }, {   "date": "2012-10-10",   "value": 36 }, {   "date": "2012-10-11",   "value": 33 }, {   "date": "2012-10-12",   "value": 31 }, {   "date": "2012-10-13",   "value": 30 }, {   "date": "2012-10-14",   "value": 34 }, {   "date": "2012-10-15",   "value": 38 }, {   "date": "2012-10-16",   "value": 37 }, {   "date": "2012-10-17",   "value": 44 }, {   "date": "2012-10-18",   "value": 49 }, {   "date": "2012-10-19",   "value": 53 }, {   "date": "2012-10-20",   "value": 57 }, {   "date": "2012-10-21",   "value": 60 }, {   "date": "2012-10-22",   "value": 61 }, {   "date": "2012-10-23",   "value": 69 }, {   "date": "2012-10-24",   "value": 67 }, {   "date": "2012-10-25",   "value": 72 }, {   "date": "2012-10-26",   "value": 77 }, {   "date": "2012-10-27",   "value": 75 }, {   "date": "2012-10-28",   "value": 70 }, {   "date": "2012-10-29",   "value": 72 }, {   "date": "2012-10-30",   "value": 70 }];  // Create axes var dateAxis = chart.xAxes.push(new am4charts.DateAxis()); dateAxis.renderer.grid.template.location = 0; dateAxis.renderer.minGridDistance = 50;  var valueAxis = chart.yAxes.push(new am4charts.ValueAxis());  // Create series var series = chart.series.push(new am4charts.LineSeries()); series.dataFields.valueY = "value"; series.dataFields.dateX = "date"; series.strokeWidth = 3; series.fillOpacity = 0.5;  // Add vertical scrollbar chart.scrollbarY = new am4core.Scrollbar(); chart.scrollbarY.marginLeft = 0;  // Add cursor chart.cursor = new am4charts.XYCursor(); chart.cursor.behavior = "zoomY"; chart.cursor.lineX.disabled = true;  // Create a horizontal scrollbar with previe and place it underneath the date axis chart.scrollbarX = new am4charts.XYChartScrollbar(); chart.scrollbarX.series.push(series); chart.scrollbarX.parent = chart.bottomAxesContainer;  chart.events.on("ready", function () {   dateAxis.zoom({start:0.10, end:1}); }); }``
``body { background-color: #30303d; color: #fff; } #chartdiv {   width: 100%;   height: 300px; }``
``<!-- Resources --> <script src="https://www.amcharts.com/lib/4/core.js"></script> <script src="https://www.amcharts.com/lib/4/charts.js"></script> <script src="https://www.amcharts.com/lib/4/themes/animated.js"></script> <script src="https://www.amcharts.com/lib/4/themes/dark.js"></script> <div id="chartdiv"></div>``

## Java: Fill bidimensional array or nested lists with permutation of dates allowing null and repeated values considering d1>d2>dn or d1

I am doing operations with `n` number of dates and I want to fill a `n``x``m` array that contains all permutations considering the following:

• The driver of the permutations has to be the relationship between dates. E.g. date1 > date2
• Null and duplicates are allowed
• Do not have duplicate permutations
• Dates are defined, not provided (this implementation detail outside the scope of question)

## Current work:

So far I split the problem in 3 general cases:

• Permutations without null values and without duplicates.
• Permutations with null values but without duplicates.
• Rest of permutations (need help with this one).

## Example to clarify the situation:

I have `4` dates that correspond to the dates of birth of: `Tim`, `Zoe`, `Liz` and `Ben`.

I want to know all the permutations for parallel universes in respect of who was born before who (date values are relatively relevant). In case of `null`, it would indicate that the person was not born; and in case of duplicates, they mean that people were born at the same date.

I split the the problem in 3 parts. I also represented the dates as numbers so I can have the representation in an array with numbers and later I can just translate those numbers to a date (maybe randomized or maybe as a part of an list of enumerated values).

As example, I will have the following enumerated dates:

``1 = 2018-01-01 2 = 2019-01-01 3 = 2020-01-01 4 = 2021-01-01 ``

The permutations for the 1st part of the problem (permutations without null values and without duplicates) should look like:

``[ Tim , Zoe , Liz , Ben ] [  1  ,  2  ,  3  ,  4  ] [  1  ,  2  ,  4  ,  3  ] [  1  ,  3  ,  2  ,  4  ] [  1  ,  3  ,  4  ,  2  ] [  1  ,  4  ,  3  ,  2  ] [  1  ,  4  ,  2  ,  3  ] [  2  ,  1  ,  3  ,  4  ]   ...[16 more rows]... [  4  ,  1  ,  2  ,  3  ] ``

For the 2nd part of the problem, we can substitute any single number with null throughout all permutations from previous step and we would still be consistent. The example would be as follows:

``[ Tim , Zoe , Liz , Ben ] [  ␀ ,  2  ,  3  ,  4  ] [  ␀ ,  2  ,  4  ,  3  ] [  ␀ ,  3  ,  2  ,  4  ] [  ␀ ,  3  ,  4  ,  2  ] [  ␀ ,  4  ,  3  ,  2  ] [  ␀ ,  4  ,  2  ,  3  ] [  2  , ␀  ,  3  ,  4  ]   ...[16 more rows]... [  4 ,  ␀  ,  2  ,  3  ] ``

The tricky part is including repeated values to indicate that more than one person was born the same date. See the examples below:

This indicates that Tim, Zoe and Liz were born the same date and they are younger than Ben.

``[ Tim , Zoe , Liz , Ben ] [  1  ,  1  ,  1  ,  2  ] ``

This indicates that Tim and Zoe were born the same date and they are elder than Ben; and Ben is elder than Liz.

``[ Tim , Zoe , Liz , Ben ] [  3  ,  3  ,  1  ,  2  ] ``

Code for 1st and 2nd parts of the problem

``public class Permuter {     static void permute(List<Integer> list, int recursionNestingLevel, List<List<Integer>> results){         for (int listIndex = recursionNestingLevel; listIndex < list.size(); listIndex++){             Collections.swap(list, listIndex, recursionNestingLevel);             permute(list, recursionNestingLevel + 1, results);             Collections.swap(list, recursionNestingLevel, listIndex);         }         if (recursionNestingLevel == list.size() - 1){             results.add(new ArrayList<>(list));         }     }     public static void main(String[] args){         List<Integer> listForPermutation = Arrays.asList(1,2,3,4);         List<List<Integer>> permutations = new ArrayList<>();         Permuter.permute(listForPermutation, 0, permutations);         System.out.println("Permutations without null values and without duplicates:");         System.out.println(permutations.stream().map(list -> list.toString()).collect(Collectors.joining(System.lineSeparator())));         List<List<Integer>> permutationsWithNulls = permutations.stream().map(list -> list.stream().map(i -> i == 1 ? null : i).collect(Collectors.toList())).collect(Collectors.toList());         System.out.println("Permutations without null values and without duplicates:");         System.out.println(permutationsWithNulls.stream().map(list -> list.toString()).collect(Collectors.joining(System.lineSeparator())));      } } ``

## Java: Fill bidimensional array or nested lists with permutation of dates allowing null and repeated values considering d1>d2>dn or d1

I am doing operations with `n` number of dates and I want to fill a `n``x``m` array that contains all permutations considering the following:

• The driver of the permutations has to be the relationship between dates. E.g. date1 > date2
• Null and duplicates are allowed
• Do not have duplicate permutations
• Dates are defined, not provided (this implementation detail outside the scope of question)

## Current work:

So far I split the problem in 3 general cases:

• Permutations without null values and without duplicates.
• Permutations with null values but without duplicates.
• Rest of permutations (need help with this one).

## Example to clarify the situation:

I have `4` dates that correspond to the dates of birth of: `Tim`, `Zoe`, `Liz` and `Ben`.

I want to know all the permutations for parallel universes in respect of who was born before who (date values are relatively relevant). In case of `null`, it would indicate that the person was not born; and in case of duplicates, they mean that people were born at the same date.

I split the the problem in 3 parts. I also represented the dates as numbers so I can have the representation in an array with numbers and later I can just translate those numbers to a date (maybe randomized or maybe as a part of an list of enumerated values).

As example, I will have the following enumerated dates:

``1 = 2018-01-01 2 = 2019-01-01 3 = 2020-01-01 4 = 2021-01-01 ``

The permutations for the 1st part of the problem (permutations without null values and without duplicates) should look like:

``[ Tim , Zoe , Liz , Ben ] [  1  ,  2  ,  3  ,  4  ] [  1  ,  2  ,  4  ,  3  ] [  1  ,  3  ,  2  ,  4  ] [  1  ,  3  ,  4  ,  2  ] [  1  ,  4  ,  3  ,  2  ] [  1  ,  4  ,  2  ,  3  ] [  2  ,  1  ,  3  ,  4  ]   ...[16 more rows]... [  4  ,  1  ,  2  ,  3  ] ``

For the 2nd part of the problem, we can substitute any single number with null throughout all permutations from previous step and we would still be consistent. The example would be as follows:

``[ Tim , Zoe , Liz , Ben ] [  ␀ ,  2  ,  3  ,  4  ] [  ␀ ,  2  ,  4  ,  3  ] [  ␀ ,  3  ,  2  ,  4  ] [  ␀ ,  3  ,  4  ,  2  ] [  ␀ ,  4  ,  3  ,  2  ] [  ␀ ,  4  ,  2  ,  3  ] [  2  , ␀  ,  3  ,  4  ]   ...[16 more rows]... [  4 ,  ␀  ,  2  ,  3  ] ``

The tricky part is including repeated values to indicate that more than one person was born the same date. See the examples below:

This indicates that Tim, Zoe and Liz were born the same date and they are younger than Ben.

``[ Tim , Zoe , Liz , Ben ] [  1  ,  1  ,  1  ,  2  ] ``

This indicates that Tim and Zoe were born the same date and they are elder than Ben; and Ben is elder than Liz.

``[ Tim , Zoe , Liz , Ben ] [  3  ,  3  ,  1  ,  2  ] ``

Code for 1st and 2nd parts of the problem

``public class Permuter {     static void permute(List<Integer> list, int recursionNestingLevel, List<List<Integer>> results){         for (int listIndex = recursionNestingLevel; listIndex < list.size(); listIndex++){             Collections.swap(list, listIndex, recursionNestingLevel);             permute(list, recursionNestingLevel + 1, results);             Collections.swap(list, recursionNestingLevel, listIndex);         }         if (recursionNestingLevel == list.size() - 1){             results.add(new ArrayList<>(list));         }     }     public static void main(String[] args){         List<Integer> listForPermutation = Arrays.asList(1,2,3,4);         List<List<Integer>> permutations = new ArrayList<>();         Permuter.permute(listForPermutation, 0, permutations);         System.out.println("Permutations without null values and without duplicates:");         System.out.println(permutations.stream().map(list -> list.toString()).collect(Collectors.joining(System.lineSeparator())));         List<List<Integer>> permutationsWithNulls = permutations.stream().map(list -> list.stream().map(i -> i == 1 ? null : i).collect(Collectors.toList())).collect(Collectors.toList());         System.out.println("Permutations without null values and without duplicates:");         System.out.println(permutationsWithNulls.stream().map(list -> list.toString()).collect(Collectors.joining(System.lineSeparator())));      } } $$```$$ ``

## Can the dates of my ticket exceed the period I can stay visa free?

I’m allowed to stay in Taiwan visa free for up to 14 days. Is it okay if my ticket will be one with arrival say on May 1 and departure on May 30, but I will go to Hong Kong for a week in between (through a separate ticket) and will not stay in Taiwan for more than 14 days at once?

## What were the first publication dates of the Big Bads in the Elder Evils?

Elder Evils (2007) is a supplement for world or multiverse-ending Big Bads published by Wizards of the Coast. It contains nine primary antagonists (see below) with a host of subordinate or supporting lesser antagonists for each. I noticed that at least one of these (based on name, form, and defining traits), Zargon, originally appeared in the D&D module B4 The Lost City.

Elder Evils does a poor job citing the source material for its contents, and I want to know when and where these first appeared in TSR or Wizards of the Coast publications (notes about appearances in other media welcome, but not the direct answer I am looking for):

1. Atropus
2. Father Llymic
3. The Hulks of Zoretha
4. The Leviathan
5. The Pandorym
6. Ragnorra
7. Sertrous
8. The Worm that Walks
9. Zargon (1982, B4 The Lost City)

## Conditional Formatting based of Dates and a blank cell

I’m trying to turn on conditional formatting for an entire row based off a ship date – today’s date < 7 days and the cell next to it is blank. I’ve tried a few different formulas but keep coming up short.

This formula below is the closest I’ve come but still doesn’t work. =AND(ISBLANK(I7),(TODAY()-H7)<7)

I7=a date that needs to be filled in at least 7 days before shipment H7=shipment date

I’m trying to use this as a reminder.