Any excel wizards?

Publius Ovidius · Apr 26, 2016

I have two columns

One that gives a planned end time and date of a particular job, the next one that gives the actual resolved time and date of that job

What I need is the next colum to show a formula that says:

If cell 2, column b time is before cell 2, column a time, then write in cell 3. " in time". If not, then write " out of time"

Does that make any sense? I am just trying to get at a formula that compares two dates/ times and tells me which is late or not.

DTES · Apr 26, 2016

=if(b2<a2,"In Time","Out of Time")

If you are happy for them to equal, i.e. exact due time = on time then add an "=", i.e. =if(b2<=a2,"In Time","Out of Time")

KingstonSeagull · Apr 26, 2016

=IF(B2=>A2,"in time","out of time")

KingstonSeagull · Apr 26, 2016

DTES said:
=if(b2<a2,"In Time","Out of Time")

If you are happy for them to equal, i.e. exact due time = on time then add an "=", i.e. =if(b2<=a2,"In Time","Out of Time")

Beat me to it!

Publius Ovidius · Apr 26, 2016

DTES said:
=if(b2<a2,"In Time","Out of Time")

If you are happy for them to equal, i.e. exact due time = on time then add an "=", i.e. =if(b2<=a2,"In Time","Out of Time")

That is quite brilliant mate. When I next see you I will buy you a pint. Many many thanks.

Dave

Publius Ovidius · Apr 26, 2016

Thanks also Kingston seagull

KingstonSeagull · Apr 26, 2016

Publius Ovidius said:
Thanks also Kingston seagull

Should do some conditional formatting too to really spice it up. Make it so that if it is in time the cell highlights green and when its not it highlights red...

Paul Reids Sock · Apr 26, 2016

KingstonSeagull said:
Should do some conditional formatting too to really spice it up. Make it so that if it is in time the cell highlights green and when its not it highlights red...

I ruddy LOVE conditional formatting.

Then again i enjoyed my Six Sigma courses so i am definitely a nerd

Publius Ovidius · Apr 26, 2016

KingstonSeagull said:
Should do some conditional formatting too to really spice it up. Make it so that if it is in time the cell highlights green and when its not it highlights red...

Oooooh...how do you do that? ( cheeky ******* look on google...I hear you mutter).

DTES · Apr 26, 2016

As an aside, I am convincing myself that contributing to this thread counts as WORK. :thumbsup:

KingstonSeagull · Apr 26, 2016

Publius Ovidius said:
Oooooh...how do you do that? ( cheeky ******* look on google...I hear you mutter).

See at the top on the home tab there will be a thing that says conditional formatting

Clcik that then click on highlight cells rule.

Then click on Text that contains.

Then type in time or out of time and then apply the colour from the drop down selection on the right.

Paul Reids Sock · Apr 26, 2016

Publius Ovidius said:
Oooooh...how do you do that? ( cheeky ******* look on google...I hear you mutter).

Highlight the cells and select 'Conditional Formatting' from the top bar. I think it should be on the Home Tab.

Select Highlight Cell Rules, and then either 'contains text' or equal to' Type in the Text and chose the colour scheme. You will have to do this for each but it will show the two in different colours and set you up for a cracking Performance Hubs

KingstonSeagull · Apr 26, 2016

Paul Reids Sock said:
Highlight the cells and select 'Conditional Formatting' from the top bar. I think it should be on the Home Tab.

Select Highlight Cell Rules, and then either 'contains text' or equal to' Type in the Text and chose the colour scheme. You will have to do this for each but it will show the two in different colours and set you up for a cracking Performance Hubs

Uh oh! We've got a geek off!

Eeyore · Apr 26, 2016

Another question on Excel for much needed help...

I need to put together comprehensive averages for my village cricket club.

I want to know a formula that will recognise an innings that the batsman was 'not out' in when calculating completed innings- without having to produce another column for said innings.

Is this possible ?

Paul Reids Sock · Apr 26, 2016

KingstonSeagull said:
Uh oh! We've got a geek off!

I'll step out of the ring - I will fall down once we get passed V/H look ups and it will be painful for everyone having to see us go there

KingstonSeagull · Apr 26, 2016

Brighton Lines said:
Another question on Excel for much needed help...

I need to put together comprehensive averages for my village cricket club.

I want to know a formula that will recognise an innings that the batsman was 'not out' in when calculating completed innings- without having to produce another column for said innings.

Is this possible ?

What's the identifier for him not being out. Sorry could you explain a bit clearer?

KingstonSeagull · Apr 26, 2016

Paul Reids Sock said:
I'll step out of the ring - I will fall down once we get passed V/H look ups and it will be painful for everyone having to see us go there

Haha I build financial models and macros for a living...

KingstonSeagull · Apr 26, 2016

Brighton Lines said:
Another question on Excel for much needed help...

I need to put together comprehensive averages for my village cricket club.

I want to know a formula that will recognise an innings that the batsman was 'not out' in when calculating completed innings- without having to produce another column for said innings.

Is this possible ?

I think what you're asking is a way of higlighting the batsman was not out but you dont want a column that says out or not out? correct?

Paul Reids Sock · Apr 26, 2016

KingstonSeagull said:
Haha I build financial models and macros for a living...

Ah nice - I have a man that does my Macros for me

(caveat - This is more a case of me admitting my lack of knowledge than showing off that 'I have a man that does it')

DTES · Apr 26, 2016

Brighton Lines said:
Another question on Excel for much needed help...

I need to put together comprehensive averages for my village cricket club.

I want to know a formula that will recognise an innings that the batsman was 'not out' in when calculating completed innings- without having to produce another column for said innings.

Is this possible ?

Quite simple if you can be completely consistent with how you write "Not Out", i.e. no extra spaces/hyphens etc. A countif formula could count the number of times "Not Out" appears and deduct this from the number of innings to get the number of times the batsmen has lost his wicket.

Got something to say or just want fewer pesky ads? Join us... 😊

Any excel wizards?

Well-known member

Well-known member

New member

New member

Well-known member

Well-known member

New member

Well-known member

Well-known member

Well-known member

New member

Well-known member

New member

Colonel Hee-Haw of Queen's Park

Well-known member

New member

New member

New member

Well-known member

Well-known member

Share this page