I need to have a count down from Column A Date with the current date and show the number in a column

desmond_Erskine

Colum A Date - has a date ( if in the future )

Colum B - should show me the countdown as per the current date

Kelly Moore

Hey @desmond_Erskine

Try this

=IF(AND(ISDATE([Column A]@row), [Column A]@row > TODAY()), NETDAYS(TODAY(), [Column A]@row))

If you prefer for the value to be shown as negative, swap the order within the NETDAYS function

Will this work for you?
Kelly

Kelly Moore

Hey @desmond_Erskine

Try this

=IF(AND(ISDATE([Column A]@row), [Column A]@row > TODAY()), NETDAYS(TODAY(), [Column A]@row))

If you prefer for the value to be shown as negative, swap the order within the NETDAYS function

Will this work for you?
Kelly

desmond_Erskine

Hi @Kelly Moore

Thank you so much this works like a charm. Apologies for my late response. As I had to travel.

I need to add the date & time to Column A, so the countdown in Column B should also show the remaining time.

Q1. I need to learn how to add the time part to the date column.

Q2. What needs to be edited to the formula you previously gave me?

Warm

Dez

Kelly Moore

Hey @desmond_Erskine

Only system columns (Created Date and Modified Date) can show the timestamp as part of the same column. To mitigate this, a time stamp must be shown as a text field. In addition, Time is not calculated as a function - it must be done be formula, piecing apart the hours and minutes. Paul Newcome has pages of formulas written on this, you can find the link here. Your request can be done - perhaps not as easily and straight-forward as one might initially think.

Where is your timestamp coming from? Do you have time durations that cross midnight? Can you share a screenshot showing the format- please show a few lines- I'm looking to see how your time is formatted, how are hours under 10 formatted, etc.

Thanks

Kelly

desmond_Erskine

Hi @Kelly Moore

Thank you for your prompt reply.

Please find the following

Column A Date

Column A1 1430 (Time)

Column B =IF(AND(ISDATE([Column A]@row), [Column A]@row > TODAY()), NETDAYS(TODAY(), [Column A]@row)) - This what you gave me previously

I hope the above helps you understand my chart

Warm Regards

Dez

Kelly Moore

Hey @desmond_Erskine

A few more questions:

Will the times entered only be daylight business hours, say anywhere from 0500 through 2000, or can it be anytime around the clock? And let's say your time is "0900"; is this always entered as 0900 and never 900? Is the time entry free text, or is it from a dropdown menu? And no one ever uses colons, semicolons or other punctuation in the entry? 09:00

If the entered time is around the clock, how is midnight entered? 0000 or 2400?

As mentioned above, time (hours/minutes) isn't the straight forward subtraction formula like what is used to calculate duration of days. I'll try to have this to you tomorrow evening

Kelly

desmond_Erskine

Hi @Kelly Moore

Thank you for your reply.

The time will be mostly 9 am to 5 pm, you can suggest to me how it best suits.

Thank you

Kindest Regards

Dez

Kelly Moore

Hey @desmond_Erskine

Thanks for the clarification. Writing the time military style is easier, it saves a step in the calculations but in the end, it doesn't matter as long as whatever you choose it is consistent. So if you say 0900, This is the format that must be used. I asked about dropdowns because that's what I sometimes do when I need people to input time. I have one sheet where I've given them the hours 0100- 2400 as choices, in whole hours. I don't bother with minutes, an hour is close enough for me. This forces consistency of data entry. Or, if I can use the Date Created timestamp, then no manual entry is required at all. The point is consistency. So I will proceed assuming your format is 1432 and I will assume you need it to the minute.

Kelly

desmond_Erskine

Hi @Kelly Moore

Thank you for the information, to the minute is fine with me.

Kindest Regards

Dez

Kelly Moore

@desmond_Erskine

Hey. Although you said counting down, all along I was thinking you were calculating time between two static timestamps. Smartsheet doesn't have the equivalent to NOW(), that is, smartsheet doesn't have a way to know what the current time is. Without the current time, you cannot count down.

desmond_Erskine

Hi @Kelly Moore

Oh ok, so the time countdown is not possible. Thank you ever so much for all your support. People like you make the world a better place to live in. Thank you.

Kindest Regards

Dez