google sheets
I'm copying and pasting things over from an old spreadsheet and the original dates where marked like '25/4' so dd/mm but I never included the year.
The problem is these where dated in 2014 but when I'm pasting them over the spreadsheet is automatically dating them in 2015. I do NOT want this.
Is there a way to prevent them from either adding on the year or to change the year?
Ultimately, you need to do what you already predicted: adding a leading zero. The TEXT function will make that happen like this:
=TEXT(A2, "00")
I've created a formula that takes on the complete column, filter for empty cells, brings together the MONTH and DAY and SORTs the lot.
=SORT(ARRAYFORMULA(TEXT(MONTH(FILTER(A2:A;A2:A<>""));"00") & " - "
& TEXT(DAY(FILTER(A2:A;A2:A<>""));"00"));1;1)
Here's a break-down description of the formula:
FILTER function will filter for all rows that have something.MONTH and DAY functions will extract the respective values
from the date.TEXT function will convert the value from point 2 into a pre-formatted STRING.ARRAYFORMULA function will apply all the above to the complete column (skipping the header).SORT function will sort the result, given by the
ARRAYFORMULA.The SORT function allows for sorting. If you do that via a column sort, the entry of the ARRAYFORMULA is taken into account as well and gets re-positioned, causing mayhem.
See example file I've created: sorting dates as text
The functions you need to use are SUMIF and COUNTIF.
For example, to count all the bulbs with DateFilterCleaned within six months, and assuming that DateFilterCleaned is within six months, you need to:
Make a helper-column which calculates the number of days eg E3 = now()-D3
And then a formula like this
=COUNTIF(E:E, ">183")
To make it general purpose for each school year, just put the start-date and end-date into separate cells (eg F1 and F2) and refer to them using absolute addressing (eg $f$1 and $f2)
(This isn't the full solution, but is hopefully enough hints to get you started. Note that there is absolutely no need to use scrips for any of this.)
Best Answer
In Google Spreadsheets, a date always refers to some year, although the year need not be shown. Otherwise it's not a date but a string. A string is stored as is, but you can't do any computations with it, and sorting will be alphabetical rather than chronological.
If you prefer to keep 25/4 as a string
Prior to posting data, format the column for the date as plain text. (From the menu: Format > Number > Plain Text).
If you prefer to have dates, but change the year to 2014
Suppose your current dates, with 2015, are in column A. Create another column with the formula
=A2-365(in the second row), etc. This column will have the dates in 2014. Copy its contents and paste as values (Ctrl-Shift-V) back to column A.If you just want to hide the year
Format the dates so that the year is not shown. From the menu: Format > Number > More formats > More date and time formats... and pick or create a format you prefer.