Home

Date Formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-482678%22%20slang%3D%22en-US%22%3EDate%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-482678%22%20slang%3D%22en-US%22%3E%3CP%3EI%20would%20like%20some%20assistance%20creating%20a%20date%20formula.%3C%2FP%3E%3CP%3EWhat%20i%20require%20is%20the%20following.%3C%2FP%3E%3CP%3EPlanting%20Date%3A%20eg-%2001-01-2019%20(entered%20as%20a%20date%20that%20a%20crop%20was%20planted)%3C%2FP%3E%3CP%3EGrowing%20Days%3A%20(if%20i%20set%20this%20as%20a%20%3DIF(xx%3D%22%22%2C%22%22%2C%3DTODAY()-xx)%20and%20the%20date%20was%2025-03-2019%20this%20would%20be%20easy%2C%20BUT%2C%20once%20todays%20date%20is%20past%20the%20harvest%20date%20it%20would%20continue%20to%20increase%20and%20if%20i%20was%20to%20reaccess%20this%20figure%20in%202020%20it%20would%20be%20300%2B%20days%20when%20it%20should%20stop%20at%2090%20days.%3C%2FP%3E%3CP%3EHarvest%20Date%3A%20eg-%2001-04-2019%20(lets%20use%20this%20date%20the%20date%20crop%20is%20harvested)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ecan%20i%20please%20have%20some%20assistance%20with%20this%20as%20im%20struggling%20to%20get%20it%20right.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-482678%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-482759%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-482759%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20so%20much%2C%20Much%20appreciated%20michael%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F324602%22%20target%3D%22_blank%22%3E%40MichaelMays%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-482744%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-482744%22%20slang%3D%22en-US%22%3E%3DIf(PlantDate%3D%E2%80%9C%E2%80%9D%2C%E2%80%9D%E2%80%9D%2CIf(HarvestDate%3D%E2%80%9C%E2%80%9D%2CToday()%2CHarvestDate)-PlantDate)%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-482738%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-482738%22%20slang%3D%22en-US%22%3E%3CP%3Eok%20another%20question%20then%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20now%20got%20a%20number%20(43581)%3C%2FP%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F324602%22%20target%3D%22_blank%22%3E%40MichaelMays%3C%2FA%3E%26nbsp%3Bwrote%3A%3CBR%20%2F%3EDo%20you%20have%20a%20harvest%20date%20field%3F%3CBR%20%2F%3E%3DIf(HarvestDate%3D%E2%80%9C%E2%80%9D%2CToday()%2CHarvestDate)-PlantDate%3CBR%20%2F%3E%3CBR%20%2F%3EOr%20I%E2%80%99d%20there%20is%20a%20maximum%20number%20of%20growing%20days%20(e.g%2090)%2C%20you%20could%3A%3CBR%20%2F%3E%3DPlantDate%2BMin(Today()-PlantDate%2C90)%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%3CP%3E%3CBR%20%2F%3Ecoming%20up%20for%20feilds%20that%20have%20not%20yet%20been%20planted.%20anyway%20to%20keep%20that%20figure%20%22%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EField%3C%2FTD%3E%3CTD%3EArea%3C%2FTD%3E%3CTD%3ECrop%20Type%3C%2FTD%3E%3CTD%3EPlanting%20Date%26nbsp%3B%3C%2FTD%3E%3CTD%3EGrowing%20Days%3C%2FTD%3E%3CTD%3EDesication%20Date%3C%2FTD%3E%3CTD%3EDays%20Desicated%3C%2FTD%3E%3CTD%3EDesication%20type%3C%2FTD%3E%3CTD%3EHarvest%20Date%3C%2FTD%3E%3CTD%3ETotal%20Growing%20Days%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ETodays%20Date%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ETwo%20West%3C%2FTD%3E%3CTD%3E163.13%3C%2FTD%3E%3CTD%3EChickpeas%3C%2FTD%3E%3CTD%3E1-01-2019%3C%2FTD%3E%3CTD%3E112%3C%2FTD%3E%3CTD%3E16-04-2019%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3CTD%3E2litres%20round%20up%3C%2FTD%3E%3CTD%3E23-04-2019%3C%2FTD%3E%3CTD%3E112%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E26-04-2019%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFarm%204%3C%2FTD%3E%3CTD%3E320%3C%2FTD%3E%3CTD%3EChickpeas%3C%2FTD%3E%3CTD%3E4-01-2019%3C%2FTD%3E%3CTD%3E110%3C%2FTD%3E%3CTD%3E19-04-2019%3C%2FTD%3E%3CTD%3E7%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E24-04-2019%3C%2FTD%3E%3CTD%3E110%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E26-04-2019%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E8-01-2019%3C%2FTD%3E%3CTD%3E106%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E24-04-2019%3C%2FTD%3E%3CTD%3E106%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E26-04-2019%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E43581%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E26-04-2019%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E43581%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E26-04-2019%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E43581%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E26-04-2019%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F324602%22%20target%3D%22_blank%22%3E%40MichaelMays%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-482734%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-482734%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20tried%20that%20and%20that%20has%20worked%20for%20me%20thank%20you%20so%20much%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F324602%22%20target%3D%22_blank%22%3E%40MichaelMays%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-482726%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-482726%22%20slang%3D%22en-US%22%3E%3CP%3EI%20currently%20have%20this%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EField%3C%2FTD%3E%3CTD%3EArea%3C%2FTD%3E%3CTD%3ECrop%20Type%3C%2FTD%3E%3CTD%3EPlanting%20Date%26nbsp%3B%3C%2FTD%3E%3CTD%3EGrowing%20Days%3C%2FTD%3E%3CTD%3EDesication%20Date%3C%2FTD%3E%3CTD%3EDays%20Desicated%3C%2FTD%3E%3CTD%3EDesication%20type%3C%2FTD%3E%3CTD%3EHarvest%20Date%3C%2FTD%3E%3CTD%3ETotal%20Growing%20Days%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ETodays%20Date%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ETwo%20West%3C%2FTD%3E%3CTD%3E163.13%3C%2FTD%3E%3CTD%3EChickpeas%3C%2FTD%3E%3CTD%3E1-01-2019%3C%2FTD%3E%3CTD%3E115%3C%2FTD%3E%3CTD%3E16-04-2019%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3CTD%3E2litres%20round%20up%3C%2FTD%3E%3CTD%3E1-04-2019%3C%2FTD%3E%3CTD%3E90%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E26-04-2019%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3EThe%20planting%20Date%20and%20HArvest%20date%20will%20be%20manually%20entered%20as%20this%20formala%20will%20be%20mulitplied%20for%20severald%20feilds%20and%20those%20specific%20date%20will%20need%20to%20be%20enter%20manually.%20I%20will%20try%20that%20and%20see%20what%20happens.%20i%20just%20used%20the%2090%20days%20as%20an%20example%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-482703%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-482703%22%20slang%3D%22en-US%22%3EDo%20you%20have%20a%20harvest%20date%20field%3F%3CBR%20%2F%3E%3DIf(HarvestDate%3D%E2%80%9C%E2%80%9D%2CToday()%2CHarvestDate)-PlantDate%3CBR%20%2F%3E%3CBR%20%2F%3EOr%20I%E2%80%99d%20there%20is%20a%20maximum%20number%20of%20growing%20days%20(e.g%2090)%2C%20you%20could%3A%3CBR%20%2F%3E%3DPlantDate%2BMin(Today()-PlantDate%2C90)%3C%2FLINGO-BODY%3E
deberi
Occasional Contributor

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
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)

I currently have this

 

FieldAreaCrop TypePlanting Date Growing DaysDesication DateDays DesicatedDesication typeHarvest DateTotal Growing Days   Todays Date
Two West163.13Chickpeas1-01-201911516-04-2019102litres round up1-04-201990   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

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

 

@MichaelMays 

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 ""

 

FieldAreaCrop TypePlanting Date Growing DaysDesication DateDays DesicatedDesication typeHarvest DateTotal Growing Days   Todays Date
Two West163.13Chickpeas1-01-201911216-04-2019102litres round up23-04-2019112   26-04-2019
Farm 4320Chickpeas4-01-201911019-04-20197 24-04-2019110   26-04-2019
   8-01-2019106   24-04-2019106   26-04-2019
    43581        26-04-2019
    43581        26-04-2019
    43581        26-04-2019

@MichaelMays 

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

Thank you so much, Much appreciated michael@MichaelMays 

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies