• 512K Members
• 6,382 Online
• 609K Conversations

## Date Formatting

Occasional Contributor

# Date Formatting

I would like some assistance creating a date formula.

What i require is the following.

Planting Date: eg- 01-01-2019 (entered as a date that a crop was planted)

Growing Days: (if i set this as a =IF(xx="","",=TODAY()-xx) and the date was 25-03-2019 this would be easy, BUT, once todays date is past the harvest date it would continue to increase and if i was to reaccess this figure in 2020 it would be 300+ days when it should stop at 90 days.

Harvest Date: eg- 01-04-2019 (lets use this date the date crop is harvested)

can i please have some assistance with this as im struggling to get it right.

6 Replies

# Re: Date Formatting

Do you have a harvest date field?
=If(HarvestDate=“”,Today(),HarvestDate)-PlantDate

Or I’d there is a maximum number of growing days (e.g 90), you could:
=PlantDate+Min(Today()-PlantDate,90)
Highlighted

# Re: Date Formatting

I currently have this

 Field Area Crop Type Planting Date Growing Days Desication Date Days Desicated Desication type Harvest Date Total Growing Days Todays Date Two West 163.13 Chickpeas 1-01-2019 115 16-04-2019 10 2litres round up 1-04-2019 90 26-04-2019

The planting Date and HArvest date will be manually entered as this formala will be mulitplied for severald feilds and those specific date will need to be enter manually. I will try that and see what happens. i just used the 90 days as an example

# Re: Date Formatting

I have tried that and that has worked for me thank you so much

# Re: Date Formatting

ok another question then

I have now got a number (43581)

@MichaelMays wrote:
Do you have a harvest date field?
=If(HarvestDate=“”,Today(),HarvestDate)-PlantDate

Or I’d there is a maximum number of growing days (e.g 90), you could:
=PlantDate+Min(Today()-PlantDate,90)

coming up for feilds that have not yet been planted. anyway to keep that figure ""

 Field Area Crop Type Planting Date Growing Days Desication Date Days Desicated Desication type Harvest Date Total Growing Days Todays Date Two West 163.13 Chickpeas 1-01-2019 112 16-04-2019 10 2litres round up 23-04-2019 112 26-04-2019 Farm 4 320 Chickpeas 4-01-2019 110 19-04-2019 7 24-04-2019 110 26-04-2019 8-01-2019 106 24-04-2019 106 26-04-2019 43581 26-04-2019 43581 26-04-2019 43581 26-04-2019

# Re: Date Formatting

=If(PlantDate=“”,””,If(HarvestDate=“”,Today(),HarvestDate)-PlantDate)

# Re: Date Formatting

Thank you so much, Much appreciated michael@MichaelMays

Related Conversations
conditional formatting with formula
kdwork in Excel on
3 Replies
Conditional Formatting - Consider Only Value as Lowest Value
Ben Smith in Excel on
2 Replies
Format Date in header and footer
Joe Cangelosi in Excel on
3 Replies
Date Formatting Won't Change
Steve Gould in Excel on
10 Replies
Conditional Formatting values across tables
ChrisJP in Excel on
5 Replies