Formula for # of days since today, if blank or if cell filled in. Want in number format?

[teenyjem]

Please see attached sheet...

Regarding Q column....

If the J column is blank, that means we haven’t received it back yet. I want to make a formula in Q, saying if this is blank, then work out how many days before today the repairer has had it (as a minus number) ie, received 09/07/12, but not here yet (imagine todays date is 12/07) so cell is blank – so it says something like ‘-3’ – also this is to be in working days only. If it ISN’T blank (we fill it in when we receive the goods back), I want it to say how many days since the ‘recieved date in column h’ til the date that will be entered in the j column. Ie. If received 09/07, and fixed 12/07, I want it to say 3 (as in a +, not a neg). Then I can use these numbers to conditionally format and create a traffic light system to quickly show what is overdue and pending etc. (But I know how to conditionally format, so the question is just on the formulae required)

Any help appreciated.
Thank you!

[Ace_XL]

How about..

=IF(J2="",-NETWORKDAYS(I2,TODAY()),NETWORKDAYS(I2,J2))

since you only want to count the number f working days

[teenyjem]

Excellent! Thanks!
Question... if i do the formula to work out from 09/07 to 12/07, it says 4 working days, which i understand, but i dont want to be inclusive of the first working day (09/07) as i am using this sheet to monitor if my repairer is late and this adds a day they cant really afford to be marked down on. Do you know where i can add something to make it 3 working days (without changing the days of receipts etc)?

[Ace_XL]

Simply..

=IF(J2="",-NETWORKDAYS(I2,TODAY()),NETWORKDAYS(I2,J2)-1)

[teenyjem]

hiya, that works when the cell is repaired cell has a date, but if it has no date, it doesnt work the same. ie. i typed 08/08 in the I2 cell and it makes the formula calculate -2, which is wrong, as the supplier wont received the unit for repair until 09/08, so i need to know how many days they have had it.

This does work ok though, for the formula if it is working out repaired date take away received date (if there is a date in there).

but we are using the formula with some conditional formatting to highlight things that have been gone between 1-5 days, 6-10 days or 15 days or more, so i want the number when at a negative to be accurate.

do u see what i mean?

sorry to be a pain!!

[teenyjem]

if you dont know what i mean, or cant fix then i suppose i could always move this row of cells and just insert another one that is literally =Q1-1, and hide the q column, but i wanted to avoid hiding cells and all that jazz if possible.

thanks
tina

[Ace_XL]

If I understand correctly, in case there is no repair date you want to calculate the differnece between today and the date received allowing for an extra day. Hence try..

=IF(J2="",-NETWORKDAYS(I2,TODAY())+1,NETWORKDAYS(I2,J2)-1)

[teenyjem]

ahhh! of course! as it is two different variables within the formula! will try that at work tomorrow!

thank you!

[tridom]

How about..

=IF(J2="",-NETWORKDAYS(I2,TODAY()),NETWORKDAYS(I2,J2))

since you only want to count the number f working days

If you are looking at number of workdays only, you may want to also exclude holidays.

I know you don't want to hide cells, but the only way I know to do it is to make a list of holiday dates, name the range where you've listed them, add the range name to the end of your networkdays(Startdate,Enddate,holidays) function, and then hide the list of dates.

[teenyjem]

i think as i work for a multinational company, even christmas wont affect us too much and it will be ok to manually change one or two as and when they occur. but thanks for the heads up!