Hi everyone,
I am trying to put together what I feel is a reasonably simple spreadsheet but my complete lack of experience with Excel is hindering me to the point where I'm struggling to even know what terms to Google in order to learn more - I apologise if this is very basic information that is widely available, but I can assure you I did spend several hours trying to work this out myself. I would really appreciate any insight anybody can offer.
The purpose of the spreadsheet is to track equipment that requires inspection on a six monthly or twelve monthly basis and represent upcoming inspection deadlines in a visually clear way.
As you can see in the attached image, it is currently able to display either CURRENT or EXPIRED by comparing the TI Expiry and EIT Expiry dates against the current date - the two expiry date columns are currently configured to display a date exactly 12 months after the date entered as the TI Date and EIT Date.
<today(),"expired","current"))
The main problem I am trying to overcome at the moment is I have no idea how to configure the Status cells to display three or even four different statements based on the proximity to the current date. I would like to display a different message in the Status cells when there is 3-12 months remaining before due date, 1-3 months remaining, less than 1 month remaining and overdue. If possible, I would like to pair this with a green/amber/orange/red shading of the entire row.
The second thing I would like to implement is configuring the Frequency cell to be a modifier on the TI Expiry cell - for example, if you enter '6' in the Frequency cell, the TI Expiry cell will show a date 6 months after the date entered in the TI Date cell and if you enter '12' it will generate a date 12 months after. I'd actually prefer this to be a drop-down box that only offers the options '6 Months' and '12 Months' but I feel like drop down boxes are probably several levels above what I am capable of at the moment though I am open to suggestions.
I very much appreciate your time if you have made it this far through my post and look forward to any suggestions anybody can offer, thank you.</today(),"expired","current"))
I am trying to put together what I feel is a reasonably simple spreadsheet but my complete lack of experience with Excel is hindering me to the point where I'm struggling to even know what terms to Google in order to learn more - I apologise if this is very basic information that is widely available, but I can assure you I did spend several hours trying to work this out myself. I would really appreciate any insight anybody can offer.
The purpose of the spreadsheet is to track equipment that requires inspection on a six monthly or twelve monthly basis and represent upcoming inspection deadlines in a visually clear way.
As you can see in the attached image, it is currently able to display either CURRENT or EXPIRED by comparing the TI Expiry and EIT Expiry dates against the current date - the two expiry date columns are currently configured to display a date exactly 12 months after the date entered as the TI Date and EIT Date.
<today(),"expired","current"))
The main problem I am trying to overcome at the moment is I have no idea how to configure the Status cells to display three or even four different statements based on the proximity to the current date. I would like to display a different message in the Status cells when there is 3-12 months remaining before due date, 1-3 months remaining, less than 1 month remaining and overdue. If possible, I would like to pair this with a green/amber/orange/red shading of the entire row.
The second thing I would like to implement is configuring the Frequency cell to be a modifier on the TI Expiry cell - for example, if you enter '6' in the Frequency cell, the TI Expiry cell will show a date 6 months after the date entered in the TI Date cell and if you enter '12' it will generate a date 12 months after. I'd actually prefer this to be a drop-down box that only offers the options '6 Months' and '12 Months' but I feel like drop down boxes are probably several levels above what I am capable of at the moment though I am open to suggestions.
I very much appreciate your time if you have made it this far through my post and look forward to any suggestions anybody can offer, thank you.</today(),"expired","current"))
