[Gadgetman]

Hi

What formula would I need to write in cell (G:4), which will add 1 to cell (G:4), if the time in (E4) is between 00:00 hours and 07:00 hours

Cell (E:4) shows the time in 24hour format.
Cell (F4) shows the cost
Cell (G:4) shows the cost of Cell (F:4) + 1 if Cell (E4) = 00:00 am to 07:00 am

Example:
E4 F4 G4
Time Cost Extra
01:30 am £2.00 £3.00



Thanks

 

[Anne Troy]

=if(and(e4>=00:00,e4<=7:00),f4+1,"somethingelse")

You didn't say what you want if cell E4 is NOT between those times. So, if you want it to be zero, replace "somethingelse" (including the quotes) with zero. If you want text there, you must leave the quotes, but a number doesn't require quotes.

 

[maxflia10]

Maybe,

=IF(AND(E4>="0:00"+0,E4<="7:00"+0),F4+1,"")

 

[XL Guru]

>> Trying is the first step towards failure

Aah, one of my favourites.

You can perhaps slim this to

=IF(E4>TIME(7,0,0),F4, F4+1)

I'd be interested to know the significance of the "+0s" in maxflia10's version (?)

Rgds,
Andy

"Don't put off till tomorrow what you can put off indefinitely".

 

[DaveBurnett]

I'd be interested to know the significance of the "+0s" in maxflia10's version (?)

Forces numeric conversion.

 

[Gadgetman]

Thanks MAXFLIA 10, that seems to have done the trick.


=IF(AND(E4>="0:00"+0,E4<="7:00"+0),F4+1,"")

However, could you please let me know how to amend this formula so that the result appears in every cell in column G as I move down the list.

Thanks.

Sorry if it seems obvious question, but I am new to Excel.

 

[XL Guru]

>> how to amend this formula so that ...

Copy & paste it down.

Or, when G4 is selected, you should see a small black square at its bottom right corner. This is the "fill handle" ; you should be able to click on it and drag it down.

I'd have thought the version omitting AND would be more efficient, i.e. "if time is < 7" is effectively the same as "if time is <7 and >0" ; maybe it didn't work for you (?)

Rgds,
Andy

 

[maxflia10]

Andy's correct in shortening the formula omitting the AND.

=IF(E4<"07:00"+0,F4+1,"")

Indeed the +0 is a coercer much like *1 or -- i.e.

=IF(E4<--"07:00"......

=IF(E4<"07:00"*1.....

 

[Gadgetman]

Hi XL Guru. Yes your formula works as well.

Having a little problem with dragging down the cell box.

Do I first click on G4, drag down the column to say G100 then paste the formula in G4?

 

[XL Guru]

Click on G4 to make it "active". Move the mouse until it's over the fill handle. Click on the fill handle, & keeping the mouse button down, drag downwards. This will copy the formula through G5, G6, etc. Might require practice if you're not used to it.

To (a) copy (b) paste ; click on G4 then on the Copy button. Then select the range you want to copy to, and click on the Paste button.

Rgds,
Andy

 

[Gadgetman]

Excellent !

Thanks alot my friend.

 

[fitzdocs]

'Nuther way to copy quickly (without having to paste) that avoids the use of the Fill Handle (AutoFill drag): simply select the whole range from the first cell (inclusive) containing the formula -- either by dragging down with the normal hollow cross pointer or by scrolling till you can see the last cell in the range and SHIFT+CLICKing it -- then choose Edit-->Fill-->Down. If you do a lot of this, CTRL+D is a worthwhile keyboard shortcut unless you've assigned it to a macro.

Shift+Click after navigating with the scroll bar is more manageable when the selection runs well off-screen, as it makes it much easier to avoid overshoot on a fast computer.

Mike