Turn Date into numeric value
I would like to follow a task and see the difference between when it was planned and actually done.
To be more precise.
Column "Agreed Date" is used on what date the two parties agreed to finish the task
Colum "Final Date" should show what day the task was really done. This should not change based on date, but should be a numeric value (or similar)
Delta should show, what was the difference between final and agreed date.
Is there an easy approach to this?
I have a total blackout on how to solve this I think pretty easy topic. THANKS
Comments
-
Hi Dan,
Try using the Netdays Function or the Networkdays function for calculating the number of days between two dates.
=Netdays([Final Date]1, [Agreed Days]1)
https://help.smartsheet.com/function/netdays
=Networkdays([Final Date]1, [Agreed Days]1)
https://help.smartsheet.com/function/networkdays
-
While the problem being provided was solved. The title is completely wrong for the question and response. I'm trying to find a real answer to how to Turn a Date into a Numeric value. I'm not looking for net days, I'm look for the numeric date value of a date, for use in a formula. Smartsheet help is being..really unhelpful
-
Hi @ielektra
You could use either the MONTH, DAY, or YEAR function to pull the number from a date column of any of those parts of the date. You can even add them together with a / between them to recreate a date:
=MONTH(Date@row) + “/” + DAY(Date@row) + “/” + YEAR(Date@row)
Or you could potentially embed one of these functions into the other formula you mentioned. Would you be able to describe your desired result in more detail, perhaps with screen captures of your sheet? (But please block out any sensitive data). It would be very helpful to see the other formula you referenced, if possible.
Thanks!
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hi, @ielektra
Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)
I hope that helps!
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
To turn a date into a numeric value (generally for establishing unique ID's), I generally pull the numbers in a yyyymmdd format then wrap it in a VALUE function.
=VALUE(YEAR(Date@row) + "" + IF(MONTH(Date@row) < 10, "0") + MONTH(Date@row) + "" + IF(DAY(Date@row) < 10, "0") + DAY(Date@row))
-
I have a habit of turning dates into a number by using the following formula…
=(YEAR([Submission Date]@row) * 365) + FLOOR(YEAR([Submission Date]@row) / 4, 1) + YEARDAY([Submission Date]@row)
Seems like NETWORKDAYS might simplify this a lot…
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!