New answers tagged

0

Suppose your Col-A data begins in A2 (with a header, say, in A1). Make sure Col-B is empty (or some other column) and then place the following formula in cell B1 (or the top cell of some other open column): =ArrayFormula({"Converted"; IF(A2:A="",, REGEXEXTRACT(A2:A,"\d+") + REGEXEXTRACT(A2:A&"0m","(\d+)m")...


0

you can try for not integer: =VALUE(REGEXREPLACE(A11; "(.+)+h ?(.+)m"; ("$2")))/60 + VALUE(REGEXREPLACE(A11; "(.+)h ?(.+)?m?"; ("$1"))) and for integer: =VALUE(REGEXREPLACE(A9; "(.+)h ?(.+)?m?"; ("$1"))) I cant get that together


1

It's very likely that Google Sheets is identifying as a date-time value and displaying it using the default date-time format for the spreadsheet region that your spreadsheet is using. You could use ISDATE, ISTEXT or other ways to verify if the value is date / text and set your formula to use the appropriate value to do the match, i.e. you could use TEXT(...


0

After a lot of searching, this comment from glenviewjeff had the answer I was looking for - Leaving breadcrumbs for myself and hopefully it will help others. It's a fairly complicated process. I started with a more complex chart for historical reasons. I had to modify my chart to get it to work. I moved around the columns on the "setup" tab of the ...


0

You want the horizontal gap between data points to be proportional to the number of days in between each row of data. I suggest that this is the result of the standard chart layout. 5 data elements This is the chart created using five data elements. 362 data elements This is the chart created by including a value for every day between 1 Jan 2021 and 27 Jan ...


2

Suppose your posted raw data were in A2:A. In B2 of an otherwise empty range B2:B, place the following formula: =ArrayFormula(IF(A2:A="",,A2:A/24/60)) Then select Col B entirely and apply the following format: Format > Number > Custom number format > hh:mm:ss /24/60 divides your raw numbers by 24 hours per day and then 60 minutes per hour. ...


Top 50 recent answers are included