IF Formula between two dates
Hi there,
I'm trying to write an IF(AND formula that will calculate the correct number of work hours based on start and end dates and % of commitment.
Row 1 = Column month being calculated (07/01/23 = July 23)
Row 2 = Number of workable hours in the month
Currently using the formula below -- it wasn't coming up with any errors at first, but now that it's a column that falls within the start/end date I'm getting an invalid operation error. If I IFERROR it, it doesn't calculate accurately.
=IF(AND([Jun-23]1 >= [Start Date]@row, [Jun-23]1 <= [End Date]@row), [Jun-23]2 * Commitment@row, 0)
TIA :)
Answers
-
I believe this to be due to your [Jul-23] column NOT being a Date data type. You are asking it to compare a Text value date (cell [Jul-23]1) to a date value ([Start Date]). This will cause an invalid data type error.
Try setting up your Row1 values in Summary Fields in the Sheet summary pane instead, then you can set them to Date data types without the rest of the [Jul-23] column needing to be a date.
Kind regards
Debbie
-
Thanks Debbie, I'll give that a try.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 377 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!