32 - Livestock spreadsheet
- or -
Post a project like this2454
$$
- Posted:
- Proposals: 2
- Remote
- #1654643
- Awarded
Description
Experience Level: Intermediate
Our business is beef production which means we have a high turnover of livestock which is purchased at various ages, fattened and slaughtered. Accordingly our data includes an animal’s date of birth (DOB), date of movement into herd (in date) and date of slaughter (out date). Invariably the out date is a date that cannot be predicted until just a few days before it.
I require your assistance to develop formulae for the following:
1. Display of age in months and days (e.g. 22m 10d), so that the age is up-to-date on any day that the spreadsheet is viewed, but which stops accruing on the ‘out date’. In attempting this myself I developed the formula below but it does not account for the clock stopping on the out date. Perhaps you can develop this further.
=DATEDIF([DOB],NOW(),"m") & "m " & DATEDIF([DOB],NOW(),"md") & "d"
2. Display of number of days in herd where ‘in date’ is 0 and the day following ‘in date’ is 1, so that the days in herd are up-to-date on any day that the spreadsheet is viewed. The number of days in herd also must stop accruing on the out date;
3. Purchased animals either already carry a ‘Farm Quality Assured’ status or they do not. In the event they do not, they will attain FQA status when they stay in herd for 90 days from the ‘in date’. With this in mind the spreadsheet requires an FQA column in which ‘Y’ denotes an FQA animal. If an incoming animal is not FQA (and I enter ‘N’ to denote this) I require a formula that will give an up-to-date display on the day the spreadsheet is viewed of the number of days remaining until FQA status is attained, i.e. on the ‘in date’ 90 days remain until an animal becomes FQA. If a non-FQA animal moves out of herd within 90 days of the ‘in date’ the 90 day countdown should stop on the ‘out date’. If and when the countdown reaches 0 days, the ‘0’ should be replaced by a ‘Y’.
I require your assistance to develop formulae for the following:
1. Display of age in months and days (e.g. 22m 10d), so that the age is up-to-date on any day that the spreadsheet is viewed, but which stops accruing on the ‘out date’. In attempting this myself I developed the formula below but it does not account for the clock stopping on the out date. Perhaps you can develop this further.
=DATEDIF([DOB],NOW(),"m") & "m " & DATEDIF([DOB],NOW(),"md") & "d"
2. Display of number of days in herd where ‘in date’ is 0 and the day following ‘in date’ is 1, so that the days in herd are up-to-date on any day that the spreadsheet is viewed. The number of days in herd also must stop accruing on the out date;
3. Purchased animals either already carry a ‘Farm Quality Assured’ status or they do not. In the event they do not, they will attain FQA status when they stay in herd for 90 days from the ‘in date’. With this in mind the spreadsheet requires an FQA column in which ‘Y’ denotes an FQA animal. If an incoming animal is not FQA (and I enter ‘N’ to denote this) I require a formula that will give an up-to-date display on the day the spreadsheet is viewed of the number of days remaining until FQA status is attained, i.e. on the ‘in date’ 90 days remain until an animal becomes FQA. If a non-FQA animal moves out of herd within 90 days of the ‘in date’ the 90 day countdown should stop on the ‘out date’. If and when the countdown reaches 0 days, the ‘0’ should be replaced by a ‘Y’.
Ben H.
100% (97)Projects Completed
66
Freelancers worked with
34
Projects awarded
29%
Last project
30 Nov 2019
United Kingdom
New Proposal
Login to your account and send a proposal now to get this project.
Log inClarification Board Ask a Question
-
There are no clarification messages.
We collect cookies to enable the proper functioning and security of our website, and to enhance your experience. By clicking on 'Accept All Cookies', you consent to the use of these cookies. You can change your 'Cookies Settings' at any time. For more information, please read ourCookie Policy
Cookie Settings
Accept All Cookies