# Tag Info

5

It is as simple as using the following sequence formula =SEQUENCE(A1) Or you even could have something like this: =SEQUENCE(A2,1,A4,A6) You could even use the SEQUENCE function for dates =SEQUENCE(A2,1,A4,A6) SEQUENCE

4

The best formula for you to use would be: =NOW()+[days to hit goal] You mention: ...turn the number of days to hit my goal into a date format (so display "04/12/2020" vs "5.8 days") You also mention in answer: I used the following formula: = TODAY ( ) + [days to hit goal] and then formatted for a date within the cell. It now displays ...

3

Instead of date 'TODAY()' use date '"&TEXT(TODAY(), "yyyy-mm-dd")&"'" NOTES: The last " should be placed in a proper place in order to have a text value as the second argument of QUERY. 'TODAY()' doesn't returns Today's date, it returns a string with the word TODAY followed by parenthesis. Try this =ARRAYFORMULA(QUERY({A3:H, HOUR(A3:A)}, "...

3

Disclaimer: I am posting this answer that doesn't exactly answer the asker's question because this is the answer I needed when this question was the first result on Google, and I want to help the next person who has the same question I did. This may not be the prettiest formula, but the result is the prettiest I could manage. Here's the Google Sheets ...

2

The general formula as you asked is: =((NETWORKDAYS(today(),eomonth(today(),-1)))*-1)-1 Using fixed values for today "02/10/2019" for dates the above formula would be: =NETWORKDAYS("30/09/2019","02/10/2019") We use eomonth(today(),-1), so we take as our starting date the last day of the previous month. We multiply the whole formula by -1 because we ...

2

Solution with a Formula : A1 : =ArrayFormula(if( mod(ROW(A1:A100),4)=1, text((round(ROW(A1:A100)/4)*7)+43730,"dddd dd mmm yy") ,) ) Solution with drag and drop Enter the dates in the cells : A1 is the first sunday, A5 is the second with this formula =A1+7 ...

2

It looks like you are looking for a single formula solution but they could be hard to understand and maintain, specially if you aren't familiar on how the Google Sheets related features works. Related features Dates. Google Sheets handles dates as serialized numbers, where a day is one unit. Because of the above feature we could create a list of dates by ...

2

You can replace both your formulas with this single one: =SUM(FILTER(B2:B,A2:A>(TODAY()-E1)-1))*POWER(COUNT(FILTER(B2:B,A2:A>(TODAY()-E1)-1)),2) Functions used: FILTER TODAY POWER Pro tip Use the following formula without the need of an extra cell. It also takes into account leap years. =SUM(FILTER(B2:B,A2:A>(TODAY()-(TODAY()-(EDATE(TODAY(),-12)))...

2

The formula you can use to increment dates by exactly one month from a given date is: =ArrayFormula(EDATE(E7,SEQUENCE(14,1,1,1))) Functions used: ArrayFormula EDATE SEQUENCE

2

How can I make a COUNTIF(or similar) that counts the amount of people join within a time period? You do not really need any regex function. You can use the different versions of the following formula instead. To find all values within the second month (February) =ARRAYFORMULA(COUNTIFS(V21:V38,"<>",MONTH(U21:U38), "="&2)) To just find the Y ...

2

Whenever any text is entered in a cell, you want to convert it to uppercase. But your script applies to all kinds of types (includes dates and numbers) and this has undesirable results. Javascript recognises a variety of types. The reason that your script isn't working as expected is that it applies to all types, rather than only to the "string" type. ...

1

Make sure to first delete excess rows before using arrayformulas... for example if you have 100 rows of data make sure you don't have more than 200 rows in your speadsheet... (highlight right click and delete excess rows) The following formula will do what you are asking for assuming row 1 is headers. Paste the following in cell F1 =arrayformula({"Final ...

1

Instead of =VALUE(A1) use =TO_PURE_NUMBER(A1) Reference TO_PURE_NUMBER

1

The problem is that Sheets is not recognizing that number as a date. It sees 20161001 as 20,161,001 which, when converted to days from the "epoch" date1, gets you December 10, 1998. You need to convert that string into an actual date that Google recognizes. If your value is in cell A1, this will do it: =date(left(A1,4),mid(A1,5,2),right(A1,2)) The date() ...

1

Solution without google app scripts 1) sort your unique dates =arrayformula(query(unique(datevalue(A:A)),"select * where Col1 is not null")) 2) mark the days that are streak days with a 1 =arrayformula(if(F:F="","",{0;if(F2:F-F1:F=1,1,0)})) 3) count the streaks =max(arrayformula(if(G:G="","",frequency(if(G:G,row(G:G)),if(not(G:G),row(G:G)))))) Note: ...

1

You could create a google app script function like this: function BIGGEST_CONSECUTIVE_DATE_STREAK() { const activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); const range = activeSheet.getActiveRange(); var currentDate; var newDate; var biggestStreak = 0; var currentStreak = 0; // Loop through all values in the selected ...

1

Answer: ... -> Edit chart -> Customize -> Gridlines -> Horizontal Axis (in drop down) -> Major gridline count Under the Major gridline count you can select or type however many dates you want to see on the horizontal label here, for auto mine shows up with a reasonable number of dates but if you wanted to see 20 dates or 10 dates you can type 20 or 10 ...

1

By one side, durations as well as dates are handled by Google Sheets as serialized numbers, by the other side, HOUR treats it's argument as a date. The unit for dates, including durations, is 1 day. To get the hours divide the duration by 24.

1

Create a new spreadsheet Go to spreadsheet settings and set Region to some country that use the date formatting that corresponds to the survey data, like Mexico. For other countries check https://en.wikipedia.org/wiki/Date_format_by_country Import the survey data Related Formatting Google Sheet Dates Inconsistent Change Google Sheets default date format ...

1

Try this // As used at: https://webapps.stackexchange.com/a/133308/ function DeleteOldEntries() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName('Current'); // assumes Current is the name of the sheet var archive = ss.getSheetByName('Archive'); var datarange = sheet.getDataRange(); var lastrow = datarange....

1

@tedinoz guided me to a different approach: instead of the custom function, I ended up using NETWORKDAYS.INTL the following way: Mondays: =NETWORKDAYS.INTL("8/1/2019",eomonth("8/1/2019", 0), "0111111") Tuesdays: =NETWORKDAYS.INTL("8/1/2019",eomonth("8/1/2019", 0), "1011111") Wednesdays: =NETWORKDAYS.INTL("8/1/2019",eomonth("8/1/2019", 0), "1101111") ...

1

By default when you use HH:MM it's a TIME type of format Example : Format is TIME : 01:00:00 Format is DURATION : 25:00:00 Test : 01/01/2019 + 25:00:00 = 02/01/2019 01:00:00 with the TIME format. 01/01/2019 + 25:00:00 = 1043209:00:00 when using the DURATION format. Conclusion : try using HH:MM:SS to avoid futur format problems with ...

1

I am used to doing this in Open Office. Being UK I used the dd/mm/yyyy format, which Google does not recognise. So if I enter 2/10/2019, adding 1 produces 2/11/2019. Choose Oct/2 as format and then you can just do =[original cell] + 1 to get Oct/3.

1

First thing to have in mind is that Google Sheets (as well as other spreadsheet apps) use serial numbers to handle dates, so when a value looks like 8/15/2019 (if it's handled as date and not as text) Google Sheets internally handle this value as 43692. Recarding COUNTIF, this function requires two arguments and if you will use a comparison operator like &...

1

One solution is to use datetime in the select vs. separate date and time specs. See linked sheets. Revised query is: ="select A, B where A > datetime '"&text(datevalue(\$A\$6), "yyyy-mm-dd")&" " &text(timevalue(\$A\$6), "hh:mm:ss")&"' limit 1" Could not find documentation for the query argument spec of date, timeofday, datetime. Just guessed ...

Only top voted, non community-wiki answers of a minimum length are eligible