Formula help – Count then number of days between a date column and today, stop counting days based on column status, but keep value

Platform discussions Special workflows & use cases
1

Hi,

I’m building a workflow or a car inventory board. I’m trying to create a formula to track how many days a car has been in storage by using a Date column for the storage start date. Currently I am using ROUND(DAYS({Arrival Date},TODAY()),0)*-1 to calculate those days, but this continues to track days even after the car has left the storage.

I need the formula to stop counting days when the Departure Date column is not blank anymore, but retain the number value it was until that date.

Any assistance would be greatly appreciated!

2

Hi @AriellaD - you could just put it in an IF statement to use the Departure Date when populated:

IF({Departure Date}=“”, ROUND(DAYS(TODAY(),{Arrival Date}),1),ROUND(DAYS({Departure Date},{Arrival Date}),1))

You also don’t need the *1 at the end to make it a positive if you flip the dates in the DAYS() function as it should be DAYS({End Date}, {Start Date}).

Thanks,
Mark

1 Like
3

Thank you @mark.anley!

This is the formula I ended up going with since I added a “storage status” column and one of the options is “departed storage”.

IF({Storage Status}=“Departed Storage”,ROUND(DAYS({Departed},{Arrived}),0),ROUND(DAYS(TODAY(),{Arrived}),0))

I have one more question for you. I’m looking to add two more columns to this board to indicate when a car has temporarily left the storage. I would like to subtract the number of days that the car was temporarily removed from storage from the final “Days in storage” column. How can I go about doing that?

4

Hi @AriellaD - by the two new fields do you mean two additional date columns (Start and End date the car was temporarily removed)? If so this should work:

IF({Storage Status}=“Departed Storage”,
ROUND(DAYS({Departed},{Arrived}) - DAYS({Temp End Date},{Temp Start Date}),0),
ROUND(DAYS(TODAY(),{Arrived}) - DAYS({Temp End Date},{Temp Start Date}),0))

5

Hi @mark.anley yes, I’ve added two additional date columns! I’m not sure why, but that formula is giving me an error.

6

@JCorrell Can you help me with this formula too?

7

@AriellaD

LOL, sure… can you share your current formula?

8

Sure! This is my current formula: IF({Storage Status}=“Departed Storage”,ROUND(DAYS({Departed},{Arrived}),0),ROUND(DAYS(TODAY(),{Arrived}),0))

The formula that Mark suggested earlier didn’t work for me. See below. I’ve added two more date columns to this board for cars that temporarily leave the storage facility and then return. I want to subtract the days the car temporarily left the storage facility from my current equation shown above.

Mark’s suggested formula:
IF({Storage Status}=“Departed Storage”,
ROUND(DAYS({Departed},{Arrived}) - DAYS({Temp End Date},{Temp Start Date}),0),
ROUND(DAYS(TODAY(),{Arrived}) - DAYS({Temp End Date},{Temp Start Date}),0))

9

@AriellaD

When you say it’s not working… is it giving you undesired results or an error?

10

@AriellaD

Mark’s function looks correct to me… The only issue I see is that it does require that all of the date columns have values.

Can you describe which dates might be blank?

1 Like
11

It’s always right @JCorrell :rofl: