Counting days between 2 dates and swapping one of the dates for another if cell if not empty - google sheets

0
votes

As the title suggests I'm wondering if there is a way to count days from 1 Column from the current day, then when filling in another column it stops counting the difference in days?

I'm using the following formula (found online)

={"Days";ArrayFormula(if(A3:A="",,int(B3:I)-left(A3:A,10)))}

Column A being where my date of entry is, column B is a hidden column with every row set to the

=Today()

I have another column, Column C that I will enter the date manually after date of completion, at which point I would like it to then stop counting the days and show me the number of days between Column A and C (Date 1 and Date 2)

I'm not overly experienced in google sheets but I'm thinking maybe there's a way to check if column C is empty, if so carry on with todays date, if it's not empty to calculate Column C - Column A? Would making an new column for checking if the completion date has been entered work?

ISBLANK(C:C)

If so would it be possible to get one formula to check whether True or False and determine what 2 dates to work with? Lets say Column D is for ISBLANK the following is what I think of

={"Days";ArrayFormula(if(CD:D="False",,int(C3:C)-left(A3:A,10)))}

={"Days";ArrayFormula(if(D3:D="True",,int(B3:B)-left(A3:A,10)))}

When I try this I have no luck and get a "Result was not expanded automatically, please insert more rows (1)" error message

Any help would be appreciated!

2
dategoogle-sheetsarray-formulasdays
for your column C you will need a timestamp script - thats the only solution - player0
Kindly share a sample spreadsheet and the desired output. - Nikko J.
@NikkoJ. docs.google.com/spreadsheets/d/… Sorry about taking a while to get back, see a quick example of the spreadsheet I've got, added some notes in the sheet to clarify what I'm trying to accomplish. - Robbie Hudson

2 Answers

1
votes

Apply the formula :

=if(B2="",today(),B2)-A2

at C2. And drag downwards. That's should do.

Please share if it work/understandable/not.

0
votes

Sorry it's a bit hard to understand this question, if you could post a current and desired screenshot example that would help I think.

So, I think your best bet here is to review this video if you're wanting sheets to auto-input a time entry when a cell is modified:

https://www.youtube.com/watch?v=548dD3iXetg

And it will show you how to script a cell to enter in a time entry when another cell is edited, and then you could use a variation of formulas to calculate the difference between the days of the original minus the date from the script (such as the once referenced below or by @p.phidot )

So for an example, once you script the time entry to work in the background of the sheet, you have a formula using an IF statement to make it pull between your today date or the new date being entered.

My cell references below are an example as I can't follow the question sheet.

=if(b2="",A2-B2,A2-C2)

This way if b2 is not blank it'll do a2-c2. You could also use ISBLANK if you like.