Job progression worksheet

Hi,

My names Sam, i'm currently trying to create a spreadsheet to manage the time line of individual jobs for my company. I am useless when it comes to excel but keen to learn so please bare with me!

I thought I would open one thread and ask all of my questions here so that anyone wishing to do the same will have a walk through guide to follow? I have read the forum rules and it does say to post a new thread for each question. Hopefully this is O.K though?


So, if anyone can help me with some relatively basic questions, I would be extremely grateful for your time.

O.K the spreadsheet comprises of multiple columns such as follows

A: Job number (example XX000000, XX00001, XX000002)
B: Job type (ideally from a drop down although using excel starter I haven't got the 'data' tab so unable to follow any online tutorials)
C: Job raised (date)
D: Survey (date)
E: Acceptance (date)
F: Allocated Person (ideally from a drop down but as above)
G: Start (date)
H: Completion (Date)
I: Re-visit (Date)
J: Re-call (Date)
K: Invoiced (Date)
L: Paid (Date)
M: Complete (Yes/No)

So as you can see, much of the spreadsheet will be a case of compiling dates which will be entered to track the progress of the job. In the future I would like t be able to use the data to calculate quote to acceptance percentages, recall rates based on the engineer who has attended etc, but walk before run. Also it would be very useful if the row would change colour like a traffic light system depending on the progress of the job if at all possible?

So, i've spent all day looking through google and youtube but I am stuck at the first hurdle. So my first question is, would anybody be kind enough to help me through this project from the top? The first question being, how do I get the job numbers to automatically add 1 to the cell above?

Thanks again for your time,

Sam

Hi Sam,

It's bear not bare.

I see you have had no responses to your question for several hours. The reason for that would probably be that whilst you know how the initial construction of the worksheet should look you have not even done that and given the experts something to work with.

You are not stuck at the first hurdle. Prepare the basics. Start a worksheet with the titles you describe with appropriate formatting.

I would like t be able to use the data to calculate quote to acceptance percentages Where is the quote on your list?

What is a recall rate? Do you mean that this is to be the percentage of recalls against the total number of jobs done by a particular engineer or against all jobs? Introduce a column for that if it is. Be specific. "etc" is no help.

What criteria do you wish to apply to the traffic light system? All the dates will follow as the job progresses but how many days are allowed before thresholds are met/exceeded?

Your first question is regarding the job number. Typically, the answer to you question would be to input in A2 "=A1+1" but this won't work with the numbers as you have them. So, tell us the exact format of the job number. How many zeroes or how many digits should the first one have? How many digits are allowed? 000000 is absurd and shows little thought. You must start at 1 or 00001 or 000001. Why does it have to have XX? Will that change? If they all have XX as a prefix then surely that has no value by way of a sortation tool and only gets in the way.

List the job types. How can we do a dropdown without any data for that and allocated person.

Are revisit and recall entries compulsory or optional? If you want these to be overridden then tell us.

If you are having problems proceeding with any of this because of limitations by way of only using starter I would suggest you speak to your boss and get upgraded.

This advice is for free and would cost a pretty penny in the real world. Invest in the full version and I and or others will help you if we can.

Hi and welcome to the forum

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem. Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the adress bar)

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

Put it this way - members often search for info basxed onm a thread title...I dont think too many searches will be made that will pull this thread title?

Looking at your post, if your questions are all based on a progressive improvement on what you have, than postin g them all here is fine. However, if some questions are on a different topic, please post a new thread for those

I did wonder.

Given that Sam is clearly very new to Excel and has done more than most to explain, may I suggest a title such as "Job progression worksheet".

I think Sam is to be encouraged and I will help all I can when he gets an upgrade!

That is probably as good as any, thanks for the assist

Hi Russell,

Firstly thanks for reply. I am sorry you feel that I haven't included enough detail. My reasoning for not doing so was to prevent the first post having an overwhelming amount of information in it which may have put potential readers off. You are right, I am a complete novice when it comes to excel but its seems to be a very powerful tool that I would like to learn how to use for future projects.

The spreadsheet in question has been created as you mention, should I have posted a copy up to show, and if so how do I do this? I have had some help from a member on here directly and it would seem as though excel starter is having a limiting effect on what I am able to achieve, unfortunately I have no 'boss' to turn too, as that person is me!

I am looking into purchasing a new computer and will upgrade to the latest Office 365 at the same time if you recommend this?

To clarify a few point you have raised;

Each job is allocated a new number on receipt of enquiry, therefore it may never even technically become a live job.. The two letters identify which contract they belong too. e.g PH being private work we receive, GU being contractual orders from one source, RA another etc. A separate spread sheet could apply to each contract if it simplified the creation.

The amount of zeroes is purely to prevent me ever running out of numbers for that particular code. Although unlikely, I have no idea what the future holds for a particular line of work, so for the sake of adding an additional zero or two it prevents a potential problem occurring in the future.

The quotation follows the survey, it may be an idea to add a new column to show when the survey was sent, although I am trying to limit the amount of needless input required to make managing the system easier. Effectively if a job were to reach the survey stage but not progress to acceptance, either the quotation was never sent or not accepted.

As said, in the future it would be nice to use the data collected to review where we succeed or fail in certain areas of business. Using the example of recall's, the amount of recalls in total compared to the total of all jobs completed. Additionally being able to spot recurring 'offenders' of recalls by the engineer assigned to those jobs would also be very useful. I am sorry if you felt as if my question was open ended, but that is purely because I am unsure exactly what is possible due to my inexperience.

Recall and revisit are optional, along with survey and acceptance for reactive maintenance works etc. The types of job may change but for a minute installation, service, repair would suffice.

With regards to the traffic light system, again being a novice I am unsure of its limitations. Having read various posts from online I was able to make a row change colour depending on a single cells value, but unable to override this colour. For example from yellow showing in progress to green for on completion, it stayed yellow. However it would change green if data was entered prior to it being yellow, if that makes sense!?

Ideally, I would like jobs which have been completed, or waiting on a response from the client to be green. (e.g. waiting for acceptance, waiting for payment, etc)

Jobs which require our input (waiting for survey, if added, waiting for quotation to be created or invoice to be sent) to be yellow.

Jobs which have had recall raised or have not had a response from a quotation within 30 days for example, would go red.

As always though I am open to suggestions if this is either not technically possible, or a better alternative is offered.

I hope this has cleared up some of the questions asked, and I am sorry for my lack of experience with Excel, or if I have offended anyone asking for help because of it. I appreciate you are spending your own free time to answer these questions. If it is any consolation I do the same on a plumbing and heating forum!

I'm not here just to knock this spreadsheet together, or hope that someone will do it for me so I can be on my way and earn the money off it if that is what you are thinking. I would like to learn how to use Excel and then have the option to create other spreadsheets from what I have learn't doing this.

Thanks for your time, it really is appreciated.

Sam

P.S I have changed the thread title as requested.

Hi Sam,

Wow, what a response. Plenty to think about but first please don't think I was criticising - I can appear rather curt sometimes but I put that down to age and grumpiness!

Whilst I am in no way near to being an expert in Excel compared to others here, this sort of project is right up my street and I'm more than happy to help you in your quest. If we come up against anything that I can't answer then we can post supplemental questions for the real experts who would not really be looking for whole projects like this.

Yes, lets start with an upload of your prepared workbook. This is an extract from the rules (you say you have read them!) (No worries) - Post a WORKBOOK. Nobody wants to type data from a picture or paste text from your post into a spreadsheet as a prelude to helping. To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

I think your original post was pretty good really and the difficulty arises when you don't know the capabilities of Excel and thus are sometimes unable to ask the right question.

I really can't stress how important it will be to your future business to ensure you have a full MS Office suite.

I specialise in developing office systems and to get this one right at the start will enable us to build in as much as possible to enable you to extract data in ways that will astound you (maybe not).

Incidentally, where are you in the UK? If you are near to me then that could make things easier. If you don't wish to say then no worries.

Upload the workbook and I'll have a look.

Cheers

Russell

First start with the order number format in A2:
="PH"&TEXT(ROW(A2)-1,"0000000")
Drag down.

Thanks bebo for your suggestion.

Sam, Bebo has made a suggestion but he has not taken into account the fact that the prefix can be one of several. PH, GU, RA etc. I can see no reason why this could not be in a separate column. Sortation or filtering you might say. No problem. Also please explain the numbering system requirements to have preceding zeroes. What do you mean by running out of numbers for that particular code? Are these numbers generated elsewhere? If you start at 1 or perhaps 1000001 it keeps it simple and limitless. So in A1 you would have PH and in B1 1000001. Sufficient for your requirements I would say unless you know otherwise.

We'll just discuss this at the moment and I'll have a look at the rest of it as we proceed.

Cheers

Russell

Thank Russell. he said :
" The two letters identify which contract they belong too. e.g PH being private work we receive, GU being contractual orders from one source, RA another etc. A separate spread sheet could apply to each contract if it simplified the creation."
so he intends to use separate sheet for each kind of contract.
Also, he use XX0000000 format to unique the job codes for tracking, i think.

Yes, point taken but I don't have in mind a separate worksheet. Well not yet.

I'm asking questions to get to the whole truth as his ideas may be too complicated right from the start and I like to keep things simple if possible.

Thanks for your suggestion. Sam will no doubt be pleased to hear from anybody who can help. Let's see what Sam has to say next.

Regards

Russell

Yep, you can use separarte sheet for each contract and data collection can be done among them.
Ayway, I suggest to use 1 sheet only for various contracts like this:
* Use A column for contract type, like "PH","GU","RA",...
* B column:Job number with:
B2=A2&TEXT(COUNTIF($A$2:A2,A2),"0000000")
Drag down.

Sam

All options you mention are possible. That's the beauty of Excel. I'd rather go for one workbook but with the modification re the number as having a prefix in a separate column. I need to understand exactly what you are ultimately trying to achieve. It is clear from what you say that this will form the basis of a database for your business from which you will extract data and statistics to drive the effectiveness of it and it's employees/agents.

That's why I'd rather have one sheet and it's best to thrash out everything right from the start so that all formulae are in place in one row that can be replicated ad infinitum without any need to modify in the future.

So now I'd like you to answer the questions I raised earlier please. i.e. Also please explain the numbering system requirements to have preceding zeroes. What do you mean by running out of numbers for that particular code? Are these numbers generated elsewhere? If you start at 1 or perhaps 1000001 it keeps it simple and limitless. So in A1 you would have PH and in B1 1000001. Sufficient for your requirements I would say unless you know otherwise.

I've done some work already. I envisage a dashboard type worksheet database where all the stats appear at the top and in full view without the need to scroll down. All data is input on that page as it occurs. It's all pretty basic at the moment.

See what you think. By the way. What do you actually do. It may help me.

Cheers

I see where you are coming from on the issue of serial numbers. Using Bebo's excellent suggestion with a little modification I have incorporated that into my worksheet.

So now, when a new job is created in Col A is input the prefix and in Col B the next number in the prefix range is generated. BUT - If that is really what you want then so be it but I have serious reservations when you are creating numbers with text and then compounding the issue by having what is essentially duplicate numbers. e.g. RA1000001 and GU1000001. Too easy to make mistakes in the future I would say and we need to get this correct right from the start and eliminate bugs during development rather than find that things don't work as we expected especially if we need to modify the w/s. To me there is no value in having the prefix and number together. A separate column will suffice and numbers will follow naturally and easier to find. It does make sense.

Without getting too technical the colouring system you are talking about is conditional formatting (C/F) and in Excel 2007 (what I use) there is the possibility of using a miriad of colours whereas in 2003 it only allowed 3. You really do need to invest in the right software to get the long term benefits.

There are other methods of raising alarms etc and I will consider these as we progress. I don't want the w/s to be too colourful as to be unfriendly to use.

Nothing to apologise for. We are going to create a w/s that will contain all contracts from which you will be able to see at a glance the current state of play. Well I hope it will.

Assuming that you accept my proposal re serial numbers, I would like to be able to chose the prefix in col a from a drop down menu. Please just remind me of ALL of the prefixes to be used and possibly some new ones for future use if the case arises.

Cheers

Russell

How about your customers details? You could have them on a separate worksheet within the same workbook. Invoices and paperwork. Templates could be created on different w/s and produced from data within the w/b. Lets not get too carried away at the moment but it's something to consider.

The traffic light system needs criteria. I would suggest that this be done based on the date "today" compared to the date of the next task although as I look at it now the only tasks that can be driven are those immediately following date raised i.e. survey and survey sent. In other words a calc based on your expectations of delivery of those tasks and "today's" date.

What's the difference between a revisit and a recall? What do they both mean? How does that impact on productivity? Which should be used to show engineers worth?

Hi Sam. That's fine. There's more for your attention so I'll leave it until you are back with more time to devote to this. I'm posting the latest version for you to look at.

Hi Russell,

Firstly, can I just says thanks for really putting a lot of thought into this for me, it really shows already and I wasn't expecting it from anyone at all.

O.k I am home now, after a 3.5 hr drive in some pretty awful weather! I am going to buy a new computer within the next few days now, I have my eye on a few so I just need to scour the net for price comparisons.

Right let me fill the gaps in the questions you have asked for. I agree with you, the numbering system would make far more sense and I would imagine, easier to implement if it ran concurrently as you have demonstrated so i am happy to run with that. Even with all jobs combined I am unlikely to ever get to 9 million jobs, and if I have I'm sure I will have moved onto another system by that time! So we will run with that, thanks.

The pre-fixes will have to be created as and when unfortunately. They don't have to, but it would be nice if they had some relevance to the contract for ease of recognition, therefore until I gain a contract I wouldn't know what the prefix would be. I have tried to do some research as best I can to gain an understanding of how the system may work. Is it possible to create a chart on another page with a list of prefixes, and set the formula to read the details of said chart and apply them to said drop down box? therefore I can add the prefix into said chart and it will appear as an option in the drop down box? (i'm sure I have seen this used before but please tell me to shut up if i'm wrong)

If this is not an option, the prefix is unlikely to bear any relevance so they can be any selection of random latter really and I can just refer to them in the future. As I said before a lot of this system is allowing my company to grow into it to prevent the system being unusable at a later stage.

Being able to keep customer details would be very advantageous, as would being able to somehow refer to PDF or word documents (our site survey forms come back electronically in PDF format) so being able to 'attach' this somehow to the appropriate date would be simply excellent. Our quotes require a written acceptance being returned which could be in word if electronically sent, or more often hard copy which would have to be scanned in on receipt. Invoices are created using a dedicated accountancy program which creates a PDF. All other correspondence would be a word document. Whether any of this is possible I wouldn't have a clue. Could they possibly be linked to a location on the computer? It would also be extremely useful to be able to search for a job by name, address, postcode etc.

Some other ideas I have thought about whilst travelling home, and please tell me if I am loading this spreadsheet up to much. Preferably, the statistics for engineers would be on another page as this would be reviewed independently at pre set intervals (monthly, quarterly etc) and so might be better off away from 'prime space' at the top of page one.

Is there a way for completed jobs which are older than 30 days to be archived somewhere/somehow? either sent to the bottom of the pile or moved to another spreadsheet? Either would suffice, but the data would need to be available for reference at a later date. For example a customer phones up 18 months after a new boiler was installed to report a fault. We would be able to look at the records, see when it was installed and what appliance it was, then advise on whether it is in or out of warranty.

Another of my wild ideas, could a system be created whereby a job can be flagged on completion somehow for a reminder (12 monthly service as an example). Using my limited knowledge, if the system somehow sorts by colour (status) the job turns red or yellow again after 12 months which brings it to the top of the list and to our attention. This allows us to be able to offer a reminder service and potentially create a job from our database?

Believe me, I have so many ideas I could go on for days. I feel like i'm asking the earth and running before I can walk, but it sounds as if you may want all the cards on the table so to speak so that these can all be implemented at an early stage. If i'm wrong then I am sorry.

Thanks again, much appreciated.

Sam

Sorry I missed part of your previous question. I agree with trying to avoid the spreadsheet being lit up like a rainbow. Perhaps a dedicated column which just contains a small square with the colour in it rather than the complete row being coloured? Or maybe just the job number changing colour to represent its status?

The criteria for this system needs some in depth thought. Please bare with me as until I understand the limitations or possibilities of excel, it is hard to decide exactly what to implement.

My initial thought was that as soon as a date is entered into a particular box, this would change a row/column/cell to the respective colour.
As an example, we receive a call for an installation which requires a survey (bearing in mind some services, repairs, servicing etc would not require this so would skip this section) the job would turn yellow until a date was entered for the survey.
Once a date has been entered into the survey sent column, it would change to green as we are then waiting for a reply from our client and no action is required. (potentially could this have a pre-defined time limit and turn a different colour if we haven't received a reply, to bring it to our attention for us to make a follow up call???)
If we enter an acceptance date its status returns to yellow, this again shows that action is required.
Once the installation is booked it can return to a different colour (depending on how many colours we are able to implement)
When a job gets completed and we don't assign a re-visit it will again turn yellow indicating action.
Sending the invoice will return it to green (or whatever colour, again if we can add a 30 day time limit to indicate non payment then superb)
Once payment is received and we close the job down as completed, it goes green and ends up at the bottom of the pile.

So in an ideal world, all green (or whatever colour) on the screen means we are on top of things and offering good service. If the screen is mostly yellow we can see that things are running away from us in the office and we are not following up our correspondence . If it's red then we know that the engineers on the ground are not pulling their weight! (which we can then assess using the statistics as previously discussed)

Now, this is purely my initial thought of how it could work using my limited knowledge and understanding that the conditional formatting allows me to sort by colour!

Again, open to suggestions entirely.

Thanks yet again, your help is priceless.

Sam

Prefixes –

We already are using the list as shown to extract prefixes. I was trying to anticipate how many are likely to be required. All I need to do is create some dummy ones that you can change at will. You will see them at B2:B9. Using them in this way rather than in a separate list allows them to double up in effect. i.e. visually they show jobs by type and also provide the data for the drop down menu. There are 8 presently. More?

Customer details –

The survey would be useful to include although my proposal could use up copious amounts of space. On the last version attached I have introduced a comments box and then formatted that to allow a PDF to be attached. (This actually made the file too large to upload so I have deleted it but pictures can be added to comments) If you have invoices produced by other software (Sage?) then there is no need to have any details incorporated here. It’s a need to know basis and the fact that an invoice has been produced should be sufficient knowledge in this w/s. I’m assuming that your accounting software also covers credit control so I have not incorporated any flagging system here.
You mention other correspondence by Word docs. We are getting into the realms of a paperless office and I would not want to go down that route to be honest. I would say that some data should be hard copy and in a file just in case! I’m thinking of an extract of the customer detail database together with relevant documentation so even the survey would be hard copy as well as on screen.


Archive

Old job can be left on the w/s but simply hidden from view.

12 month reminder

New columns introduced for this based on completion date of job. Easy to change if you require.

General

I have tried to keep things as simple as possible as I am aware of your need to get yourself upgraded as well as your systems. No offence meant. What I have created is not all that sophisticated and uses some pretty basic formulae but beauty is in the eye of the beholder. You will no doubt be blown away by some of the capabilities whereas experts will say “Pah, I could do it better”. I’ve no doubt that’s true but it’s not bad for starters. As I said at the start, development of this type of system is right up my street. I need you to be able to understand how it all works, so you really do need to upskill as I won’t be as available in the future.

Have a nosy around the latest. I'll think about the c/f.

Hi Russell,

I've just come back from PC world although unfortunately empty handed as they have no stock of the computer I wanted.

What can I say, this is already far beyond what I had imagined possible with Excel. I will see if I can hunt this computer down tonight online as like you say I really need to be able to get to grips with some of this, and I am not expecting you to do it all for me (although obviously i'm more than happy with the help received)

Is there a book or online resource you would recommend that shows you what is capable in Excel? I think this is my biggest issue, not knowing what can be done, to then be able to learn how to do it?

O.k, back to your spreadsheet. It looks very promising. I see how you have used the box now to populate the contracts. 8 is Plenty. It will take me a long time to go through that many contracts I should imagine, could this be modified in the future if required?

I couldn't quite figure out how to use the reminder function, I clicked on the cell but the formula appeared so I quickly pressed ESC to prevent breaking anything!

I cannot see the attachment in the comments box. A long term goal would be for a PDA type system to be used by engineers. Would there be any way of something being able to talk to and add comments to this box that you know of? Not at all required now, so just a simple yes/no would suffice!

I use a cloud based accountancy program called Xero to invoice, track payments etc. You are right, there is no benefit in duplicating the records this program already creates.

Is it possible to create a shortcut to a location on my computer and have this link attached to a customers file?

I suspect in practice, jobs which are 24 months old could be cut and pasted to another location at predefined intervals to archive. I should imagine thousands of lines would effect the running of the program, or will it make little difference?

Finally, when you refer to c/f? what does this mean, sorry?

I will go and hunt the internet for the computer. Thanks yet again for your time and effort.

Sam

Sorry - c/f = conditional formatting i.e. traffic lights

Reminder is automatic based on a calculation between date of completion and "today".

Dont worry - you wont break anything - whatever you download could but I and the system still have the originals.

The attachment had to be deleted as I think I said as it took too much space for downloading. Something to consider if you intend to use it to store PDF files on the workbook which would slow things considerably after time and not housekept i.e. forgotten!

Yes, I've always said that you don't know what you don't know until someone shows you. I'm self taught over many years, using this forum and other internet sites to solve problems. I wouldnt know where to start as far as learning is concerned now.

My main strength is being able to appreciate the environment and apply the paper world to computer at the same time as appreciating the capabilities of Excel which you are now experiencing, bit by bit.

Is it possible to create a shortcut to a location on my computer and have this link attached to a customers file? Dont understand.

I suspect in practice, jobs which are 24 months old could be cut and pasted to another location at predefined intervals to archive. I should imagine thousands of lines would effect the running of the program, or will it make little difference? Little difference for our purposes.

Hyperlink - I'm sure it can be done. May need help.

O.k, back to your spreadsheet. It looks very promising. I see how you have used the box now to populate the contracts. 8 is Plenty. It will take me a long time to go through that many contracts I should imagine, could this be modified in the future if required?

- You can change any code in that list and it will appear in the drop down.

I couldn't quite figure out how to use the reminder function, I clicked on the cell but the formula appeared so I quickly pressed ESC to prevent breaking anything! -

Automatic. Eventually I will lock all cells for preservation when they are not to be interfered with but for now I have left everything open for you to view the formulae. You understand what is going on and so I hope you will be able to pick up some of the formulae.

I cannot see the attachment in the comments box. A long term goal would be for a PDA type system to be used by engineers. Would there be any way of something being able to talk to and add comments to this box that you know of? Not at all required now, so just a simple yes/no would suffice! -

Out of my league when you are talking about PDAs.

Is it possible to create a shortcut to a location on my computer and have this link attached to a customers file?

Dont understand Do you have customer files elsewhere? Worried now!

I suspect in practice, jobs which are 24 months old could be cut and pasted to another location at predefined intervals to archive. I should imagine thousands of lines would effect the running of the program, or will it make little difference?

Depends on amount of work you have and the extent of the w/s. Suck it and see.

Traffic lights - To avoid psychedelia taking over I recommend only having colour when action is required.

Hyperlinks easy. Select cell E16< select "Insert" tab< Hyperlink - follows on from there to find file

Latest version attached.

You had previously asked for guidance on tuition. I've had a look around and this guy is pretty good.

http://www.exceltutorial.info/

Hi Russell,

O.K I have the new computer & Excel 2013 and my bank account is now considerably lighter. I also have a bigger monitor which helps as the entire sheet fits on the screen with ease. I haven't really had a chance to look around properly as it's taken me a while to set it all up and thought I should respond to your questions first.

Right I have had a think and I will probably not be in your good books for saying this! My gut instinct is that the job number should be created in the customer database and then a copy should be applied to the jobs page. Thinking about how this would work, a customer makes an enquiry, their details will have to be added first and foremost and the contract is allocated at the same time. If the job number is created on the jobs screen then it could technically continue through the process without any customer details being added to the database?

Ref: the hyperlinks, can this be created by a formula? For example, if I bulk create files based on job numbers 10000201 etc and store them in the same place, is it possible for a hyperlink to be created using the set location of the file e.g c:/business/customerfiles/jobnumbers and then have a formula apply the job number e.g c:/business/customerfiles/jobnumbers/10000201. Provided the file exists (which would require me to keep on top of ensuring they have already been created prior to reaching that job number) I can then add all the relevant files to this location which can be sourced through the w/b using the link so easy to access for administrators (like my misses) who might struggle to find it otherwise?

It keeps the systems separate so the files are secure which avoids problems like I think you had considered earlier, such as reliance on a system to do everything, and the potential to lose it all also?

I will have a look at your suggested guide, thanks for taking the time to sniff it out. If you don't mind, I will have a tweak with your latest copy, I probably won't be able to get half of it to work, but if I can send it over for your opinion I would be grateful of your opinion? Hopefully taking it to bits will give me an insight as to how the formulas work.

Thanks again, hopefully I can start being some use now I have a proper copy of excel!

Sam

Probably best to have a manual input. Do it quickly with Ctrl+; (Just Control and semi-colon). Carry on tinkering and I'll catch up on Monday.

Cheers

Russell

I am having some issues with a cell that is checking for 2 values. It is where a date is entered.

If F14 is empty, G14 needs to be empty.

If F14 has a date in it (F14="<01/01/2000") I need the G14 to remain empty or if this is not possible "-".

If F14 has a date exceeding 45 days (TODAY()>F14+45) I need "EXPIRED" to be entered into G14

So, I have used (googled it) =IF(TODAY()>F14+45,"EXPIRED",IF(F14=">01/01/2000","","")) The problem I can see is that the answer to =IF(TODAY()>F14+45 is "EXPIRED" But this also answers the other criteria of ">01/01/2000" so one is prioritised over the other? Is this correct?

It's possibly very simple, but my head is still coming to terms with all of the possible formulas!

Thanks in advance

Sam

Let see if F14 = "31-Dec-2009" or ="02-Jan-2010" (FALSE or TRUE from 2nd IF), it alway returns TRUE from 1st IF and result is "EXPIRED".

So the meaning IF should be stated as:

=IF(TODAY()>F14+45,"EXPIRED","")

Hi Quang, I hope you are well?

Thanks for the reply, you just caught me before I go off to sleep for the night.

The problem with this formula is that when F14 is empty, you get EXPIRED in G14 which can be quite distracting to the w/b! This was where my logic came from to add IF(F14=">01/01/2000","","") also. As said though, the problem with this is I think that Excel see's F14 with no data as +45 from (TODAY() ?

What's are your thoughts?

By the way, I have used ">01/01/2000" in the w/b in other formulas and it has worked (i:e only counts cells with a date, disregards empty cells)

Thanks

Sam

Hi Sam, good luck with new computer, new Excel .

I think you can try this :

=IF(OR(F14="",F14<DATEVALUE("01/01/2010"),F14>=(TODAY()-45)),"","EXPIRED")

Maybe:
=AND($G14>DATEVALUE("01/01/2010"),$I14="")

Hi Quang,

That also worked! Thanks. I have tried to do this:

=AND($G14&B14>DATEVALUE("01/01/2010"),$I14="") but it didn't work, any ideas?

It's quite frustrating, trying to understand this 'excel language' !

Really appreciate your help.

Thanks

Sam

Hi Sam,
What do you expect from (G14&B14)? a date?
If yes,
G14&B14 gives number (date) stored as Text, to convert to number as per one of below:
--(G14&B14)
or
1*(G14&B14)
or
(G14&B14)+0
or
VALUE(G14&B14)

then replace in your formula
and now it can be compared with number (DATEVALUE)
else,
kindly give example of G14,B14.
nice to help.

Hi,

Sorry I should have explained my question a little better. So effectively I am after this result

($G14>DATEVALUE("01/01/2010") AND ($B14>DATEVALUE("01/01/2010") AND ($I14="") I:E Conditional formatting only applies if all are true.

Does that makes sense?

Thanks

Sam

OK now use Excel expression:

=AND($G14>DATEVALUE("01/01/2010"),$B14>DATEVALUE("01/01/2010"),$I14="")

Hi,

It hasn't worked. From what I can tell, because there is a formula in G14 [ which is =IF(OR(F14="",F14<DATEVALUE("01/01/2000"),F14>=(TODAY()-37)),"","EXPIRED") ] it is reading ($G14>DATEVALUE("01/01/2010") as TRUE?

I have tried to change the date to compensate but it has not worked.

If I delete this formula from G14, it work's.

Thanks

Sam

So G14 is either blank or text "EXPIRED", it is not able to compare with a number (DATEVALUE of 01/01/2010, stored as 40179).
Can you try another expression for the CF?

Originally Posted by nostrum

G14 is either blank, has a date in it (manual input) or "EXPIRED" depending on another cells value (F14)

I think it is a poor design for G14 if it can be deleted by manual input. If F14 changed, there are no more formula in G14 to update.
I suggest using another cell, i.e W14 for manual input, then G14 formula can read value from that cell depending on F14.

I may give some ideas for CF, if you can upload a newest example. We can work with more details on that.