Re: Condition formatting - if 01/01/1900 replace with blank
cheers.. perfect
Re: Condition formatting - if 01/01/1900 replace with blank
cheers.. perfect
Hi,
i have looked around the search option and am struggling to get my head around this...
I am trying to format a cell that displays result of
Exam date (Q3) x retest period in years(R3) = due date (=Q3+(R3*365.25))
however if the exam date and retest date are blank, i am getting 01/01/1900 in the retest field
I have worked out the 3 main formats of Red <today, Amber today >30, Green > 60 however want the 01/01/1900 cell to be black or blank if applicable...
sorry for appearing stupid...but would really appreciate some help on this one...
Thanks
Andy
I have a spreadsheet with some very simple data in that i am trying to chart.
I am trying to automate the bars colours based on the results of a cell
Attached is the spreadsheet as an example... top graph is the linked data and the bottom is what i am trying to replicate..
Thanks
in advance
Andy
Hi,
I am looking in a spreadsheet to add a specific number of days to a cell result based on another fields entry.
Example
Pass date Refresh period (years) Expected Result
23/09/2013 1 23/09/2014
01/07/2014 5 01/07/2019
Tried formula
=IF(R4=1,"Q4+365",IF(R4=2,"q4+730","r4+1825"))
I am getting the result of r4+1825 in the cell not the pass date plus 5 years...
Please help
Andy
I have a sheet that logs when orders are placed, i need to establish if they are on a day or a night shift.
My shifts are:
Days: 07:00 to 19:00
nights: 19:00 to 07:00
my raised field is in column N starting at N2
the data is stored as follows:
05/01/2009 12:55:00
which would represent a day shift...
my problem is that the night shifts start 5 hours into the previous day than it ends...
can anyone help me with a formula that would cater for this...
thanks in advance
Re: percentage of
thats what i calculated too but my mind was just saying "NO"... I can't start to explain how bad my day has been
Thanks
Chimp
I have a simple problem that i can't get my head around.
I am trying to display how much i have reduced accidents by.
2010 - 116 accidents
2011 - 83 Accidents
i think i am just over tired cant seem to get it rightforum.ozgrid.com/index.php?attachment/43128/
Re: vlookup problem, if statement
forum.ozgrid.com/index.php?attachment/42191/
In the strippeddown example you will see the problem is in Row k12 the figure displays 17.0 whereas as it should actually be zero given the employee has worked less than 1 year.
I therefore need this to read zero, else do the lookup
Re: vlookup problem, if statement
ok i have tried the following....
=IF(F8=0,0,VLOOKUP( WEEKS DUE'!$A$2:$AU$52,F8+1,FALSE),"Person under 17 or Less than 1 year")
however i get an error arounf the 0,0,VLOOKUP area...
Sorry for being a pain here
Re: vlookup problem, if statement
ok after much thought, what i need this formula to read is much simpler than my stupid original mindset....
If f8 =0 then 0 else run vlookup, how do i type this please?
Hi,
I am using a lookup which works fine to a point....
=VLOOKUP(J8;'Weeks Due'!A$2:AU$51;F8+1;FALSE)
J8 referes to "Projected Age Years"
F8 refers to "Projected Service Years"
however i am getting some funny results if the "F8+1 = 17"
this is all to do with some strange spreadsheet to calculate bonus payment, however you only get paid the bonus if more than one years service and over the age of 17...
how can i modify the vlookup to read, "=vlookup...work your magic, however if the f8 result=17 then "0")
hope that makes some sense
Andy
I have a column of data which is all in the same format :
Example
01.01.01
01.01.02
01.01.03
Etc
This relates to a storage location I have shelf/bin/divider
The problem I have is I need to change the ". For -"
01-01-01
And so on, I have tried the normal find and replace however the result then changes to a date form.
I am happy to insert a column to add a formula to resolve this if needed, anyone help please.... This is driving me crazy
Re: adding "-" character to string of numbers
PERFECT!!
Thanks
I have alarge list of numbers that are in the wrong format for me to use in my search format.
the list is as follows:
Cell "A1" = 1234567890
I need cell "B1" to display "1-234-56-789-0
My numbers in Column A are alsways 10 digits
and always adopt the same format as per B2 requirements, 1 digit, 3 digits, 2 digits, 3 digits, 1 digit seprated by a "-"
can someone please advise how this can be achieved
Andy
Re: Create Match List From Another Worksheet
wow that was bloody quick.......thanks..........that sorts out part one of my multi chunk project...Thanks..
i now need to assign some dates, and a few rules to my list...
In my Spreadsheet i have now added a dates Worksheet..
First Dates
I need to assign a Fixture to a date where they fixture type is League.... not assigning dates to the dates that those that are described as Cup...
i have had to add the dates sheet in a separate file due to file size...hope ya get the idea..
Can you assist please?
Thanks for ya help
Andy
I need to create a full list of fixtures for a football (Soccer) league i am involved in.
i believe this is a multi stage project that i need to tackle in bite size chunks.
I have a list of teams, division that they play in and there home stadium name.
my spreadsheet has 2 sheets visible at the mo...
Rules:
I need to create a list of all games that require to be played.
All teams in the same division must play each other twice, once at the home stadium of each club.
Only division 1 clubs play against other division1 clubs
Only division 2 clubs play against other division2 clubs
I need a full list of matches to be generated in my worksheet called "required Matches"
can this be done via a macro? if so i really could do with some help please.
i have a word document that i am trying to semi automate a few of the functions i have put on the page...
Example...
In the attached document you will see i have three drop down boxes, which i believe could technically be reduced to just 1...
this is where my problem starts.
i want to be able to change JUST the salary drop down box and both the grade and the shift premium would then adjust automatically.
Can this be done..
the thing i am struggling with is where would i store the data to say that if the salary is £14,527 the Grade would be "A" and the shift premium would be "15%"
please any help on this matter would be much appreciated.
at the bottom of the attached document are a list of some of the grading structures: you will see that the unique item is the Salary! field
also posted at :
Re: Current year + 3 years
ok i have sorted that now thanks, but have stumpled across another issue here....
can i ammend the formula :
=DATE(YEAR(B5)+3,MONTH(B5),DAY(B5))
so that if B5 is null then the cell D5 will be null please
cheers
Andy
Re: Current year + 3 years
i have done that but as you will see from my example attached....
the cell turns red, even though the retest date is 2007
this particular cell should not turn red until 15/03/2007
please advise
Cheers
Andy
I am trying to get a cell to display the refresh date for retesting....
Example
Test 1 ----- Test date 15/10/02 ------ Retest Date ---- 15/10/05
I have the first 2 bits and need a formula to calculate the additional 3 years for the retest date...
i then need this to be formatted to change colour from a green cell to red colour 3 months before the test date, so in this case on the 15/7/05 the cell would change to Red.
i assume this could be done via conditional formatting, but need some help please.
Many Thanks
Andy