Google-sheets – Blank Box If No Data Inputted

formulasgoogle sheetsgoogle-sheets-arrayformula

This might sound hugely amateurish but I have a spreadsheet for my Pilot Log Book as I am a student. I have boxes stating my Take Off time and Landing time. In this case:

13:40 (H5) to 14:40 (I5)

This means I would put down 1:00 (K5) for the amount flown.

What formula would I input to say "I5 – H5 =", but if there is no value in I5 or H5, leave K5 blank?

Best Answer

=IF(AND(H5<>"", I5<>""), I5-H5, )

=ARRAYFORMULA(IF((H5:H<>"") * (I5:I<>""), TEXT(I5:I-H5:H, "hh:mm"), ))

Related Solutions

Google-sheets – Google Form keeps changing Spreadsheet cell entry

The problem was due to Google Forms adding a new row every time.

If you are having the same problem here's what you need to do to fix it:

Where you previously had your formula change it to:

=arrayformula('YOUR SHEET NAME'!A2:A)

Change the sheet name, and enter the cell you want to target, as-well as the column.

Make sure you delete all other formulas in that column.

This will automatically post the new data from the cell into the one below it. Just make sure you have no other formulas in the column. Also make sure the cell you target is the one where the form data is stored.

Google Apps Script – Run Locally to Process Spreadsheet Data

Apps Script code is executed on Google servers. There is no way to run it locally.

calculates everything in one go which returns the results for all cells

This is probably what you should do, based on pretty vague description in the post. If you have calls to methods like setValue within a loop that runs many times, that should be changed to array manipulation.

Best Answer

Related Solutions

Google-sheets – Google Form keeps changing Spreadsheet cell entry

Google Apps Script – Run Locally to Process Spreadsheet Data

Related Topic