New answers tagged google-sheets-dates
1
Ok I've worked it out. Google Sheets imports the date cell as text and then interprets that cell as a date. The Locale is set as UK so it can't handle the US format date. I had change the spreadsheet Locale to US, use the =TO_DATE(INT(A2)) formula and then create a custom format on that column to show the date in UK format.
0
Most likely your data is mixed or some cells have been formatted before entering your data.
Debugging
Use the ISNUMBER function to check wether all your dates are correct.
Go to the top menu and Format > Number > Automatic and reformat your cells.
(If you still have issues, please share a sample sheet)
0
From
https://support.google.com/docs/answer/3294949?hl=en
WEEKNUM(date, [type])
type - [ OPTIONAL - default is 1 ] - A number representing the day
that a week starts on as well as the system used for determining the
first week of the year (1=Sunday, 2=Monday).
So, weeknum(date,2) will give week starting with Monday.
0
Create a clone table but in the cells that would import the numbers use =0+reference_cell.
Example: let's assume that Dates is recorded in A1, 10/21/2019 is recorded in A2 and so on, therefore Arugula is recorded in B1, 4 is recorded in B2 et cetera. In such case, if e.g. AA1 is blank you may select it and type =A1, then drag-and-drop AA1 to the right and ...
1
Instead of using "Wednesday" (or "Friday") you can use the WEEKDAY function in combination with the SWITCH function.
=SWITCH(WEEKDAY($A3),4,"workday",6,"workday","---"))
You can also add to this formula the ARRAYFORMULA function so your results will auto-expand for a whole array.
=ArrayFormula(SWITCH(WEEKDAY($A3:$A),4,"workday",6,"workday","---"))
...
0
EDIT
You can use the following formula for eg. cell J16:
=REGEXREPLACE(J16,"at ","")
Then copy and paste on top Values only. Then change the format.
December 23, 2019 08:31PM is what you need.
Please make sure you don't have the cell formatted as text. You can have it either at Automatic or as Number> Date time.
Read more about custom date and time ...
1
There is something missing from your formula.
You did not take into account the ID1 variable in sheet Items.
This is the corrected formula for cell C2 in sheet Products:
=IF($B$2:$B <> "",ARRAYFORMULA(VLOOKUP(MAX(FILTER(FILTER(Items!$C$2:$C,Items!$A$2:$A = B2),FILTER(Orders!$B$2:$B,Orders!$A$2:$A = A2) >= FILTER(Items!$C$2:$C,Items!$A$2:$A =B2))),{...
Top 50 recent answers are included
