Any excel wizards?

[Dave the OAP]

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]

=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]

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

 

[KingstonSeagull]

=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!

 

[Dave the OAP]

=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

 

[Dave the OAP]

Thanks also Kingston seagull

 

[KingstonSeagull]

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]

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

 

[Dave the OAP]

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]

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

 

[KingstonSeagull]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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.