More

Symbolizing by year, when date column has MM/DD/YYYY?


I am trying to symbolize an SDE feature, so I can't add any new columns. The date column is formatted as MM/DD/YYYY. I would like to symbolize all 2015s as one symbol, 2016, 2017 as other unique symbols. I figured I could go to unique values and shift click all the relevant rows together and group them, but since month is in front, the sorting just shows all the January months together, so I can't get it to sort nicely before grouping.

Is there a way to symbolize just by date?


I don't have SDE, but since it sounds like you only have a few years, you should be able to select a year and create a layer from that selection. Or just add the SDE feature multiple times and use different definition queries.


I would tackle this using ArcMap's Query Layers to leverage the power of your database. You won't have to export and make and store a derivative layer where it can possibly go stale (needing to be updated once source data changes). Open ArcMap, click File-->Add Data-->Add Query Layer

  1. Build the SQL Query (you may need to look up equivalent functions if not using MS SQL Server)

A wizard will open, pick your SDE connection and the database view that represents your feature class. You will then see all the columns for that feature class in the right hand side. From here you can write a SELECT statement to create a new column of just year data. I use Microsoft SQL Server as my database, here is a sample SELECT statement that works in MSSQL:

SELECT Objectid, /* you need a unique identifier, use this one if it already exists */ Name, /* Add in as little or as many columns you want from the original feature class */ Date, /* Example where date is stored as text in DD/MM/YYYY format */ RIGHT(Date,4) as Year, /* we use the RIGHT() function to just take the last four characters */ created_date, /* Example where date is stored as a datetime column */ DATEPART(yyyy, created_date) AS Year2, /* We use the DATEPART() function to operate on a datetime column and we can pluck out just the year. */ Shape /* you need the shape column to display the feature class' geometry */ FROM Maintenance.dbo.PARCELS_VW /* View that represents the Feature Class */

In this statement I want to show you what to do if you your date stored as text or in a date time column. The Date column is my date stored as text, in the format of DD/MM/YYYY. The line after the Date column is where I use the RIGHT() function to trim the last four digits off of the date text and display it as a new column called "Year". The format for using the RIGHT() function is: RIGHT(COLUMN,Number of characters you want to return). If your date is stored in a datetime column, look to the line created_date is on. The line after that is where I use the SQL DATEPART() function to return just the year and display it as "Year2". The format for using the DATEPART() function is: DATEPART(date arguments,COLUMN). Using YYYY for the date argument gives you the four digit year, see https://technet.microsoft.com/en-us/library/ms174420%28v=sql.105%29.aspx for a list of all the valid arguments available. Don't forget to include the FROM clause, this is where you state which table/view (or in GIS speak, feature class) you are going to pull these columns from. Optionally you can add a WHERE clause after the FROM clause to limit what data you pull in. Click next after you have this set up, use the Validate button first to check that the statement is valid.

  1. Step through the remainder of the Query Layer wizard

On this next page you will have to choose your unique identifier column (in our case it is Objectid). If you needed to you can select multiple columns to create a composite unique identifier, but for ArcGIS data stored in SDE we can just use the Objectid. Also verify that your geometry is correct (polygons, points, etc) and that the spatial reference it was it should be. Using the SHAPE column in the select statement is what autopopulates this date for you, just verify that it all looks good. Click Finish and the layer is added to your map, as the data is loading a window will pop up saying it is calculating the spatial extent. I usually don't wait for the calculation and just click the button to use the layer's spatial reference. Once in ArcMap you can then open up the properties and symbolize by Year.


I would do the following:

  1. Export the table (txt)
  2. Open in excel or similar
  3. Add column for year
  4. Use datepart to parse out the year
  5. Delete extra columns (leaving only year and UniqueID)
  6. Save as txt.
  7. Join back to the feature class in an mxd and symbolize by your new year column.

I believe this should be fairly easy to automate using ModelBuilder to keep all the functions in the ArcGIS Desktop environment. It could then be exported to python and run via a scheduled task if required.


You can make a query layer as suggested. Include a select statement in your definition that somehow converts your date to a numeric (calculate days likeiif(x.sampledate is null,999999,DATEDIFF(day, x.SAMPLEDATE, GETDATE())) as 'DAYS', convert to UTC, etc) so you can use values to render. This works in mapserver services and in feature server services. However, joins and relates are not supported nor can you edit a feature service layer when using this approach.

If you are using ArcGIS OnLine or Portal there is a better approach using Arcade. You essentially create an custom attribute expression using date ranges and then symbolize based on that. Here is a link to the relevant article: https://support.esri.com/en/technical-article/000017958


Auto-updating column in Google Spreadsheet showing last modify date

Trying to figure out a way to have column two auto-update to show a timestamp of the last update. I realize the doc shows a timestamp of the last modification, but we have a lot of clients accessing and adjusting the doc at the same time. So this request was put in. I'll admit I have not done coding, so this is beyond me.

Myanda had posted something that seemed along the right path, but we are not sure how to implement it for our needs:


4 Answers 4

As already said, there's no standard. However, if there was one, it would look differently.

When using YYYY-MM-DD for dates, then you really should not use QQ-YYYY for quarters. The point for the ISO field ordering is that they're ordered according to their weight: Year is more important than month, and so on.

This way you can sort the dates as string and it's chronologically sorted. That's more than just an implementation detail. The user can easily see that

are descending, while this is rather impossible with other formats.

So your quarters should look like

if you really want to use a single column for them. Using multiple columns was said to be better, but IMHO it depends. For sorting and filtering, one column works fine (text-fragment filters like -18 , 1999 , or Q4 are easy to use).

When it comes to standards, we can distinguish two types, de jure and de facto. The first type is the one defined by some established authority, whereas the second one is the type that emerges in practice over time, often informally.

While the predominant standard in date and time formats is ISO-8601, currently it does not explicitly specify a format for quarters. Something curiously similar, admittedly little to do with your issue, is the week-of-the-year part of the specification, allowing formats along the lines of 2018-W30 . Actually, ISO allows for variations, thus giving the implementation some freedom.

Putting ISO aside, due to its dominant status, it is very probable that any standards you encounter are just local or partial. You can see this in the following explanation in Investopedia:

Not all companies use the uniform quarter standard. For example, Wal-Mart Stores' first quarter is February, March, and April Apple Inc's Q1 is October, November, and December Microsoft Corporation's Q1 is July, August, and September etc. In addition, certain governments use different quarter systems. For example, the first quarter of the United States federal government’s fiscal year is October, November and December, Q2 is January, February and March, Q3 is April, May and June, and Q4 is July, August and September. State governments, also, may have their own fiscal calendars.

Whereas it talks about processes, and not writing formats, it is indicative of the variance you might encounter in your search. At the end, I'd suggest that using an intuitive approach as the one you suggested, or constructing yourself a format along the lines of ISO's week formats (thus 2018-Q2 or 2018Q2 ), might be your best hope to be aligned to some standard. However, notice @maaartinus' comment demonstrating that the ISO-influenced variation is more workable when it comes to sorting, even when the data is treated as strings.


Geography Search Option

Select from the following geographical search options: ZIP Code, Address, City, County, and State. A five digit zip code or state entry is required for all searches except the NPDES ID "Exact Match" option.

The entry can be one or more digits. It allows you to enter the zip plus-4 extension as well.

Enter a complete or partial street address.

Enter a complete or partial city name.

Enter a complete or partial county name and a two-character state postal abbreviation.

Enter a two-character state postal abbreviation. A list of the postal abbreviations and the state names is provided below. Currently all states are reporting to ICIS, except for New Jersey and Wyoming. Please note that New Jersey is not supplying EPA with required data about its Clean Water Act program as it has not converted to ICIS-NPDES. EPA has copied New Jersey's data from the old PCS system as of November 29, 2012. This allows users to see the list of regulated facilities and associated historical activities, however, subsequent state activities are not being reported. Wyoming will begin flowing data to ICIS-NPDES in February or March of 2013.

Postal Abbreviations and State Names
AK = Alaska AL = Alabama AR = Arkansas
AS = American Samoa AT = Atlantic Offshore AZ = Arizona
CA = California CO = Colorado CT = Connecticut
CZ = Canal Zone DC = District of Columbia DE = Delaware
FL = Florida FM = Federated States of Micronesia GA = Georgia
GB = George's Bank GE = Gulf of Mexico East GM = Gulf of Mexico
GU = Guam HI = Hawaii IA = Iowa
ID = Idaho IL = Illinois IN = Indiana
JA = Johnson Atoll KS = Kansas KY = Kentucky
LA = Louisiana MA = Massachusetts MD = Maryland
ME = Maine MH = Marshall Islands MI = Michigan
MN = Minnesota MO = Missouri MP = Mariana Islands
MS = Mississippi MT = Montana MW = Midway Islands
NC = North Carolina ND = North Dakota NI = No. Marianas Isl.
NE = Nebraska NH = New Hampshire NJ = New Jersey
NM = New Mexico NN = Navajo Nation NV = Nevada
NY = New York OH = Ohio OK = Oklahoma
OR = Oregon PA = Pennsylvania PR = Puerto Rico
PW = Palau RI = Rhode Island SC = South Carolina
SD = South Dakota SR = Saint Regis Tribe TN = Tennessee
TT - Trust Territory TX = Texas UT = Utah
UM = U.S. Minor Islands VA = Virginia VI = Virgin Islands
VT = Vermont WA = Washington WI = Wisconsin
WV = West Virginia WY = Wyoming

Note: The searches are highly dependent on the geographic location, and a five digit zip code or state entry is required for all searches except the NPDES ID "Exact Match" option.


I guess you're talking about Google Spreadsheets.

You can do that if you go to FileSpreadsheet settings. and change the Locale:

To change the date format to dd/mm/yy - go to your Google Apps gear wheel -> settings -> Language -> click the drop down box and select English (UK) - that gets you dd/mm/yyyy by default. Sorry if you are anything but British, but that seems the best way to do it as a user. I'm trying to do it for a whole domain to save users using different date formats and getting snarled up - not found how to do it yet!

There is a setting in Google Admin control panel -> Domain settings -> Language, which I've set to English (UK), but I've noticed one of my users is still coming up showing mm/dd/yyyy format. Meh.

Linked

Related

Hot Network Questions

site design / logo © 2021 Stack Exchange Inc user contributions licensed under cc by-sa. rev 2021.6.28.39592

By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.


Cleaning up date strings in Python

Working from a CSV of about 14.000 lines, I need to match dates in the CSV against some API. I'm reading the CSV using Pandas (for various other reasons). Most often, the date is just a year, i.e. an integer, which Pandas converts to a float. Other date formats also occur (see main in the code below). 12-10-1887 is the 12th of October, not the 10th of December.

The API also returns date strings, often also incomplete, for example when only year but not month and/or day of birth are known. Single-digit days and months do get a leading zero, and format is as close to ISO-date as possible, e.g. March 1675 will be "1675-03".

I think my best bet is not to convert to type datetime , but to try to match strings.

EDIT: because this way I can let the API do more work.

Without the date, I get all Anil Gupta's. With it, I get all Anil Gupta's with either born year (most likely), death year, of year of activity 1956. All in all, this is MUCH less work than writing dateconverters for fields that are empty in 90% of the cases. In general, name + exact date of birth is a pretty unique identifier.

So I wrote this dateclean.py (Python 3.7):

The list dates in main() contains all formats known until now. On the plus side, by using this method, adding new formats is easy.

There are a lot of patterns[x] , which is not at all DRY, yet I don't see how I can avoid that: I have to split each input string into year, month and day, if any (using r'1' etc.)

Also, I know flat is better than nested, but I don't see how I can improve on that. I do try to return as early as possible.

Readability is more important to me than performance, because the API is a bottleneck anyway. Then again, serious slowing-downers should be avoided.


Spreadsheet format¶

To produce 360Giving data in a spreadsheet, it is possible to start with an empty spreadsheet and construct the column titles (and any additional sheets), using the information given below. However, for many people, the starting point is the spreadsheet template described below.

Spreadsheet template¶

For convenience we provide a 360Giving Spreadsheet Template that can be used directly, or adapted to your needs. The spreadsheet template provided is Excel Workbook file type (.xlsx) but may be converted into OpenDocument Spreadsheet file type (.ods) for publication and use if preferred.

The template is a multi-sheet spreadsheet, and each sheet is described below.

Many data producers will be able to fit all the information about a single grant on one row of a spreadsheet. In fact most data producers do exactly that, and provide a single sheet with many individual grants.

Where data producers have more complex information, for example where a grant has many beneficiary locations, we call this a One to many relationships . Information about how to create data with One to many relationships is described below.

The 360Giving Spreadsheet template consists of a ‘grants’ sheet which contains the most common data fields.

The Additional fields section provides details of all other possible fields that can be reported. (These are derived from the 360Giving JSON Schemas ).

Meta Sheet¶

We also provide a version of the 360Giving Spreadsheet Template with the Metadata template included. The ‘Meta’ sheet may be used to publish authoritative metadata about the publisher, the file or dataset. The term we use for this is a ‘data package’. The ‘Meta’ sheet includes sections for:

  • The version of the 360Giving Schema used for the file
  • The title and description of the file
  • The dates when the file was first issued and last modified
  • Information about the publisher such as name, logo, website and identifier
  • Links to access and download the file
  • A link for the open license that applies to the file

Grants Sheet¶

The main ‘grants’ sheet includes sections for:

  • Basic information about the grant
  • Planned dates for the grant
  • Planned dates of the activity
  • Details of the recipient organisation
  • Details of the funding organisation
  • The location of beneficiaries
  • Details of the grant programme funding is from

Additional fields¶

The main ‘grants’ sheet only includes the most common information used by most data publishers. For many people this is enough.

The other sheets in the 360Giving Spreadsheet Template provide the details of all the possible fields that can be reported. These sheets serve a dual purpose:

As a way to add more information to our ‘grants’ sheet

The column titles in the extra sheets provide a handy mapping from the JSON Schema to a more human readable form, showing us all of the possible fields available in the 360Giving Data Standard.

You can use any of these column titles on your main ‘grants’ sheet if you wish.

As a way of providing information about One to many relationships

If, when creating your data, you only need a few additional fields from the additional sheets, you can simply copy them from one sheet to another.

If you have additional data to report that does not fit any of the columns provided in the spreadsheet, it is okay to create your own column titles in order to report it.

Naming your own columns.

If you are adding your own column titles it is best to use simple titles and to avoid special characters which could cause problems in data reuse.

Using only lowercase and uppercase alphabetical characters ( a-z and A-Z ), numerical digits ( 0-9 ), colons ( : ), parentheses ( ( and ) ) and single spaces will help to avoid problems. Full-stops ( . ) are known to cause issues and should be avoided. Other characters could be used, but haven’t been fully tested in all possible situations.

Actual Dates¶

When did this grant activity actually take place. Dates should be in YYYY-MM-DD format. A date range can include a start date and duration in months, or a start and end date.

Title Description Type Required
Actual Dates:Title The title of this event string False
Actual Dates:Start Date All events should have a start date. Dates should be in YYYY-MM-DD or date-time format. If the month or day are not available, these may be omitted. string False
Actual Dates:End Date An end date for the grant. Dates should be in YYYY-MM-DD or date-time format. If the month or day are not available, these may be omitted. string False
Actual Dates:Duration (months) The duration of the grant, in months. Must be in number format. number False
Actual Dates:Description A description of this event string False
Actual Dates:Last Modified When was information about this event last modified? A full date-time should be given. Usually this can be generated automatically by the software managing or exporting this data. date-time False

Planned Dates¶

When the recipient organisation intends this activity to take place. A date range can include a start date and duration in months, or a start and end date.

Title Description Type Required
Planned Dates:Title The title of this event string False
Planned Dates:Start Date All events should have a start date. Dates should be in YYYY-MM-DD or date-time format. If the month or day are not available, these may be omitted. string False
Planned Dates:End Date An end date for the grant. Dates should be in YYYY-MM-DD or date-time format. If the month or day are not available, these may be omitted. string False
Planned Dates:Duration (months) The duration of the grant, in months. Must be in number format. number False
Planned Dates:Description A description of this event string False
Planned Dates:Last Modified When was information about this event last modified? A full date-time should be given. Usually this can be generated automatically by the software managing or exporting this data. date-time False

Funding Org¶

Title Description Type Required
Funding Org:Identifier A globally unique identifier for this organisation. This is important to enable data on funders and recipients to be linked up across different grant-makers. The Organisation Identifier Standard guidance explains how to create this ID, based either on the known company or charity number, or upon identifiers held in the grant-maker’s internal systems. string True
Funding Org:Name Organisation name string True
Funding Org:Department The department or sub-unit of this organisation making or receiving the grant. string False
Funding Org:Contact Name The contact person at this organisation. string False
Funding Org:Charity Number Registered charity number, if applicable. string False
Funding Org:Company Number Registered UK company number, if applicable. string False
Funding Org:Street Address Building number and street name. string False
Funding Org:City City or town. string False
Funding Org:County County string False
Funding Org:Country Country string False
Funding Org:Postal Code Postal code (please try and provide a post code whenever possible) string False
Funding Org:Phone Number Contact phone number. string False
Funding Org:Alternate Name An alternative name for this organisation (e.g. trading name) string False
Funding Org:Email The email address for this organisation. string False
Funding Org:Description A short description of this organisation and its area of work string False
Funding Org:Organisation Type A description of this organisation string False
Funding Org:Web Address A web address for the Organisation uri False
Funding Org:Last Modified When was the organisation information for this grant last modified? A full date-time should be given. Usually this can be generated automatically by the software managing or exporting this data. date-time False

Recipient Org¶

Details of the recipient of this grant.

Title Description Type Required
Recipient Org:Identifier A globally unique identifier for this organisation. This is important to enable data on funders and recipients to be linked up across different grant-makers. The Organisation Identifier Standard guidance explains how to create this ID, based either on the known company or charity number, or upon identifiers held in the grant-maker’s internal systems. string True
Recipient Org:Name Organisation name string True
Recipient Org:Department The department or sub-unit of this organisation making or receiving the grant. string False
Recipient Org:Contact Name The contact person at this organisation. string False
Recipient Org:Charity Number Registered charity number, if applicable. string False
Recipient Org:Company Number Registered UK company number, if applicable. string False
Recipient Org:Street Address Building number and street name. string False
Recipient Org:City City or town. string False
Recipient Org:County County string False
Recipient Org:Country Country string False
Recipient Org:Postal Code Postal code (please try and provide a post code whenever possible) string False
Recipient Org:Phone Number Contact phone number. string False
Recipient Org:Alternate Name An alternative name for this organisation (e.g. trading name) string False
Recipient Org:Email The email address for this organisation. string False
Recipient Org:Description A short description of this organisation and its area of work string False
Recipient Org:Organisation Type A description of this organisation string False
Recipient Org:Web Address A web address for the Organisation uri False
Recipient Org:Last Modified When was the organisation information for this grant last modified? A full date-time should be given. Usually this can be generated automatically by the software managing or exporting this data. date-time False

Beneficiary Location¶

Information about the location of beneficiaries. Further information about beneficiaries can be provided through classifications.

Title Description Type Required
Beneficiary Location:Identifier Location identifier string False
Beneficiary Location:Name A name for this location. string False
Beneficiary Location:Country Code The ISO Country Code of the location of this activity. string False
Beneficiary Location:Latitude The latitude of a point location number False
Beneficiary Location:Longitude The longitude of a point location number False
Beneficiary Location:Description A description of this location. This could include details of the element of the activity that takes place here. string False
Beneficiary Location:Geographic Code A code referring to a geographical area, drawn from an established gazetteer. For example, the code for a local authority ward, or parliamentary constituency. string False
Beneficiary Location:Geographic Code Type The type of Geographic Code (geoCode) used (e.g. Ward, Parliamentary Constituency etc.). This value for this field should be drawn from the codelist of geographic code types. string False
Beneficiary Location:Last Modified When was this location information last modified? A full date-time should be given. Usually this can be generated automatically by the software managing or exporting this data. date-time False

Funding Org:Location¶

Title Description Type Required
Funding Org:Location:Identifier Location identifier string False
Funding Org:Location:Name A name for this location. string False
Funding Org:Location:Country Code The ISO Country Code of the location of this activity. string False
Funding Org:Location:Latitude The latitude of a point location number False
Funding Org:Location:Longitude The longitude of a point location number False
Funding Org:Location:Description A description of this location. This could include details of the element of the activity that takes place here. string False
Funding Org:Location:Geographic Code A code referring to a geographical area, drawn from an established gazetteer. For example, the code for a local authority ward, or parliamentary constituency. string False
Funding Org:Location:Geographic Code Type The type of Geographic Code (geoCode) used (e.g. Ward, Parliamentary Constituency etc.). This value for this field should be drawn from the codelist of geographic code types. string False
Funding Org:Location:Last Modified When was this location information last modified? A full date-time should be given. Usually this can be generated automatically by the software managing or exporting this data. date-time False

Recipient Org:Location¶

Title Description Type Required
Recipient Org:Location:Identifier Location identifier string False
Recipient Org:Location:Name A name for this location. string False
Recipient Org:Location:Country Code The ISO Country Code of the location of this activity. string False
Recipient Org:Location:Latitude The latitude of a point location number False
Recipient Org:Location:Longitude The longitude of a point location number False
Recipient Org:Location:Description A description of this location. This could include details of the element of the activity that takes place here. string False
Recipient Org:Location:Geographic Code A code referring to a geographical area, drawn from an established gazetteer. For example, the code for a local authority ward, or parliamentary constituency. string False
Recipient Org:Location:Geographic Code Type The type of Geographic Code (geoCode) used (e.g. Ward, Parliamentary Constituency etc.). This value for this field should be drawn from the codelist of geographic code types. string False
Recipient Org:Location:Last Modified When was this location information last modified? A full date-time should be given. Usually this can be generated automatically by the software managing or exporting this data. date-time False

Related Document¶

Title Description Type Required
Related Document:Identifier An identifier for this document. string False
Related Document:Title The document title string False
Related Document:Web Address The URL of the document. uri False
Related Document:Description A description of the document string False
Related Document:Document Type A document category. For example, ‘Application Form’, ‘Photo’ or ‘Project Report’. In future, 360Giving will provide a codelist of document types. string False
Related Document:Last Modified What was this information last modified? A full date-time should be given. Usually this can be generated automatically by the software managing or exporting this data. date-time False

Classifications¶

Title Description Type Required
Classifications:Vocabulary A vocabulary used for this classification. string False
Classifications:Code A codelist value in the chosen vocabulary. string False
Classifications:Title The title of this classification. string False
Classifications:Description A description of this classification. string False
Classifications:URL A web link to more details of this classification. uri False
Classifications:Last Modified When was this grant classification information last modified? A full date-time should be given. Usually this can be generated automatically by the software managing or exporting this data. date-time False

Funding Type¶

Title Description Type Required
Funding Type:Vocabulary A vocabulary used for this classification. string False
Funding Type:Code A codelist value in the chosen vocabulary. string False
Funding Type:Title The title of this classification. string False
Funding Type:Description A description of this classification. string False
Funding Type:URL A web link to more details of this classification. uri False
Funding Type:Last Modified When was this grant classification information last modified? A full date-time should be given. Usually this can be generated automatically by the software managing or exporting this data. date-time False

Grant Programme¶

Title Description Type Required
Grant Programme:Code An identifier for this grant programme. string False
Grant Programme:Title The title of this grant programme. string False
Grant Programme:Description A description of this grant programme. string False
Grant Programme:URL A web link to more details of this grant programme. uri False
Grant Programme:Last Modified When was the link between this grant and its grant programme last modified? A full date-time should be given. Usually this can be generated automatically by the software managing or exporting this data. date-time False

Transactions¶

The 360Giving Data Standard also allows for the reporting of three types of transactions:

These do not currently have nice human readable titles, but can still be added as spreadsheet columns if needed.

To create the column titles, refer to the 360Giving JSON Schema and use the JSON pointer paths as column titles. e.g. commitmentTransaction/0/id

One to many relationships¶

Each of the sections of additional fields above can have multiple occurrences for one grant. There are three ways of describing this in a spreadsheet.

Additional sheets¶

Use the other sheets in the 360Giving Spreadsheet Template. These have the columns described above, plus an extra column at the start for the Identifier of the relevant grant.

For the Funding Org: Location and Recipient Org: Location there is also an extra column for the Identifier of the relevant Funding/Recipient Org.

Numbering¶

You can describe multiple occurrences within the Grants sheet by having multiple columns. Use :<num>: instead of a : . This imitates JSON Pointer’s approach.

e.g. to have two related documents with their own title and web address:

Multiple Rows¶

You can place the additional information about a grant in an additional row. Use the same Identifier for the grant, and place the additional information in the relevant columns. Consuming applications will then be able to try to merge the information into a single record, so be careful not to place contradictory information in fields that cannot have more than one value (e.g. a title or description)

Field guidance¶

Dates and times¶

360Giving requires you to provide information on when a grant was awarded, and allows you to add details of when a project is taking place, and when you last updated information about aspects of the grant.

There are three different rules for validating dates:

Full dates (Award Dates and Transaction Dates)¶

The Award Date must provide a full date, including year, month and day in YYYY-MM-DD format (e.g. 2017-04-02 for the 2nd April 2017).

In some cases, award date data exported from grant systems includes the time of the grant, using a date-time format (e.g. 2017-04-02T16:45:00Z - a grant made at 4.45pm).

Note - The time component is never significant in Award Dates or Transaction Dates. Applications should ignore the time component when processing grants data.

You can set Excel to present a date column in YYYY-MM-DD format using a custom format as described here.

Uncertain dates (Planned Dates and Actual Dates)¶

Other events in the lifetime of a grant, such as for when the funded activity will take place, may include less specific date information. Funders should aim to be as specific as they can be, but do not need to guess at the particular day or month when an activity will take place if they are not certain or do not yet know.

Dates in the Planned Dates and Actual Dates groups should be provided in YYYY-MM-DD format, but the day or the day can be dropped or on the year provided (e.g. YYYY-MM or YYYY).

For example, if an application only indicates that a project will start in May 2019, then the Planned Dates:Start Date value may be �-05’.

It is up to users of the data to judge how to interpret dates which only include a year, or year and month. Different applications and analysis may require different judgements.

Date-time (Last Modified dates)¶

All rows in a 360Giving spreadsheet, and all objects in the JSON structure, can have a Last Modified date.

If used, this must always be in full date-time format so that if multiple updates take place on a single day, consuming applications can work out which version to use.

You can set Excel to present a date column as a full date-time using the custom format of “yyyy-mm-ddThh:mm:ssZ”. If you also set the formula for the entire column to `=Now()` then this value will be refreshed automatically every time you save the file.

Conformance¶

In order to conform with the spreadsheet standard:

  • Read the column definitions carefully and follow the format they request - for example, formatting identifiers and dates according to the standard. Full reference information is provided below.
  • Provide an Identifier for each grant
  • Update the Last Modified date whenever the status of a grant changes
  • Remove or hide non-required columns that you are not using - although make sure you check for any hidden columns before publishing your data, and always remove rather than hide sensitive information.
  • Re-order the columns so that information is arranged in the way you want
  • Add extra columns to include information you want to share, but that is not covered by the standard. (See Additional fields ).
  • Move columns in the 360Giving Spreadsheet Template between sheets.
  • Add extra rows at the top of the table
  • Change the field names provided by the standard

We are facing the same problem (logging into the backend). What is very strange:

  1. M1 (not only 1.9) shops are facing this problem / no M2-shops
  2. Only one of our hoster / server is having this problem - M1 shops on other hosters servers are running. (We are in contact with the hoster whether there has been an automatic update of the PHP Zend module. I will keep you updated.)
  3. The problem suddenly appears from 05. May 2020 without changes on the shops or on the server

Edit with current solution: I've edited the /lib/Zend/Locale/Format.php replacing all iconv_strpos with strpos and it works for now.

Edit of the Edit: We replaced it with mb_strpos now which should be better for unicode characters.

Promised update on the server update: It seems that there have been some updates but whatsoever it would not make sense to downgrade any module in order to keep the shop running. So changes on the shop in order to comply with the updated requirements make more sense INMHO.


Addressing Errors

When working in Query Editor, you might run into errors when converting data, modifying applied steps, or taking other actions. Often you won&rsquot realize that there is an error until you try to apply any changes you&rsquove made. (You should be applying and saving your changes regularly when transforming data.)

To apply your changes in Query Editor, click the Close & Apply down arrow on the Home ribbon, and then click Apply. Any changes that you made since they last time you applied changes will be incorporated into the dataset, unless there is an error. For example, when you apply the changes to the hawks dataset after promoting the headers, you will receive the message shown in the following figure.

When you click the View errors link, Query Editor will isolate the row that contains the error, as shown in the following figure. The TagID value for this row is 263. After viewing the error row, you should see that the Wing column contains an NA value, although the column is configured with the number data type, which is why you received the error.

Click hawks on the left to exit out of the Errors screen and get back to the Query Editor.

To address an error in Query Editor, you can replace or remove the error or change the column&rsquos data type:

To change the column&rsquos type, click the type icon next to the column name, and then click Text.

To replace the error, right-click the column header, click Replace Errors, type a replacement value, and then click OK.

To filter out the row with the error, click the down arrow next to the TagID header, search for the 263, and then clear the checkbox associated with this ID.

To remove all rows that contain errors, click the table icon in the top left corner of the data set, and then click Remove Errors. This is the approach I took for the hawks dataset. As a result, the Removed Errors step was added to the Applied Steps section.

NOTE: One thing interesting about the error is that it occurs as a result of promoting the first row to column headers and the subsequent type conversion that came with it. From what I can tell, Query Editor must use only a sample of the data when selecting the data type during this step. It does not appear to have anything to do with the Data Type Detection setting in the preview window. I tried all possible settings and the results were always the same.


Priority 1: Fix the table. You should not be storing date and time data, in varchar columns, and quite possibly should not be separating date and time into their own separate columns. You should also avoid reserved words/keywords as columns, but I suspect you may have just dumbed down your actual table structure.

Priority 2: Stop using BETWEEN. This can cause all kinds of issues, especially if you fix the table and combine the date and time into a single column. The end of a range is different depending on the data type, but using >= beginning of the range and < beginning of the NEXT range will always work. Consider that the table has changed, you can say as follows (and I'm not sure what you're trying to do with your second and third where clauses, but you should just be able to look at the date column):

Priority 3: Stop comparing dates as strings. If you can't fix the table, then your clauses need to first convert the string columns into valid date/time values, so you aren't comparing strings. And you need to use string formats that are safe in SQL Server dd-mm-yyyy is not one of those formats - it can break (or just return the wrong data) if you change language settings, for example.