Google-sheets – Formula required for date and counting fabrics

google sheets

How do create a formula that counts the days I am waiting for fabric to arrive, however when it does arrive I need it to stop counting the days.

=TODAY()-TO_DATE(A41)

This works great, however it doesn't stop counting even after fabric has been delivered.
Suggestions?

Best Answer

I think your question is incomplete. To work out how long you have been waiting so far we need to know when ordered and when is ‘now’ (ie TODAY()). To work out whether or not to stop counting we need to know whether ‘now’ is before or after actual arrival. For ‘actual arrival’ a flag would serve (say ‘delivered’ rather than ‘awaited’ or nothing) but in practice a third date (that of arrival) is likely to be most convenient.

So in chronological order there are two scenarios:

  1. Still awaited: ordered – today –delivered, and
  2. Received: ordered – delivered – today

For which an IF construction is well suited.

Assuming A41 contains the ordered date (as such, to avoid the need to convert to a number with TO_DATE), the system helps us with TODAY but somewhere (B41 assumed here) if not a flag another date is required – that of delivery.

So we can stop counting when B41 is in the past (less than today): 

=if(B41<today(),""

Where I have interpreted “stop counting” as ‘show no result’, to draw attention to what is still on order. If you wanted to continue to show the delivery interval after delivery, change to: 

=if(B41<today(),B41-A41

But if not in the past we continue counting, for which an else condition can be added: 

=if(B41<today(),"",today()-A41)

You might want to adjust by 1 depending upon whether to include or exclude the delivery date itself in the count.

Related Topic