Future OnHire Date to equal Zero or be blank

[Danin]

Hello,
I have a cost sheet. and I realised I now have to put in future dates for items that will be delivered but i don't want the costs to show until after the date has arrived. I had a formula to show the dates and the overdue dates but i need to add to this formula. Any ideas?

K4 Date Hired 2017/07/05
L4 Forecast Off Hire Date2017/07/19
M4 Date Off Hired
N4 Overdue Days0
O4 Total Days Hired 14
P4 Receipt Number
Q4 Daily Rate $211.59


See the above, I want it to show zero days hired. because the hire date has not arrived.
I have following formula for that column
=IF(ISBLANK(M4),L4-K4+N4,M4-K4)

what can i add to show Zero (0) in "Total Days Hired" column if the "Date Hired" is in the future please?

 

[AlanSidman]

=if(k4>today(),0,today()-k4)

 

[NARAYANK991]

Hi ,

To add to what Alan has posted , you might want :

=IF(K4 > TODAY() , 0 , IF(ISBLANK(M4) , L4-K4+N4 , M4-K4))

Narayan

 

[Danin]

I need the costings to be as of current date.
K4 Date Hired 2017/07/05
L4 Forecast Off Hire Date2017/07/19
M4 Date Off Hired
N4 Overdue Days0
O4 Total Days Hired 14 <<< this should not have a quantity in it if future date only once HIred date has occured should quantity start increasing
P4 Receipt Number
Q4 Daily Rate $211.59

my current formula states that if "M4 Actual Date Offhired is empty, then keep adding days until "M4" has an offhire date in it.
it works fine with dates from past up to current date. i am trying to add onto this formula to say. If the date has not occured yet, do not enter any quantity for Days hired.

Does this make sense? I am not the best at explanations.

 

[Danin]

apologies in advance explaining outloud so i can understand. ok. so your formula is saying if my onhire date is more (larger) than todays date return the value 0.
it is putting zero(0) in even if it is last years date or yesterdays date? missing a step. i always have problems trying to make a small formula long. giving it multiple parameters to consider.

 

[NARAYANK991]

Hi ,

Have you tried both the formulae that have been posted ?

If you upload a workbook with enough data in it , and with a few manually entered outputs , you can get a solution in quick time.

Narayan

 

[Danin]

=IF(K6 >TODAY(), 0,IF(L6>TODAY(),.......IF(ISBLANK(C6), B6-A6+D6, C6-A6)))

i am trying. i need it not to show me total, only total to current date.

 

[Danin]

ok that is not correct
i want to have a cell that shows me the current total days i have hired an item for.
i have a start date.
the forecast end date is only to allow me to highlight any overdue items. or items close to being due.
that is where i am making my mistake. i have a start date, i need to show that if "K6 is in the future than total days hired will equal "0"
if "K6" is in the past, than total days hired will equal todays date minus "K6" date. when actual offhire date is put in, than it will show final total not today minus start date.

this would mean my formula could look like:

=IF(K6 > TODAY(), 0, IF(ISBLANK(M6), TODAY()-K6+N6, M6-K6))
it works i think. can you streamline this to allow me to learn a more compact formula?

 

[Danin]

ok, managed to get that to work but the empty cells are showing the date in general form. in other worksheets i usually put in IFERROR, or ISBLANK but it keeps telling me i have too many arguments. think i need to quit for day and look at it tomorrow.

 

[Danin]

no idea if this is correct, but it works out. i have a column that has a daily rate. it must always have a daily rate for any hire.
i used this column to say if it was blank than instead of showing todays date code, show an empty cell. and i realised it had to be at beginning of formula not end by trial and error. If someone has a neater and more concise formula i would love to see it and learn from it.

=IF(ISBLANK($Q4),"",IF($K4 > TODAY(), 0, IF(ISBLANK($M4), TODAY()-$K4+$N4, $M4-$K4)))