Formula Help

[jensloth13]

Hello, can someone please help me with a couple of formulas? Is it possible if a word in a certain cell can generate a number in a different cell? For example, if the word DOG is in cell A1 then B1 would generate the number 1, but if cell A1 had CAT, then cell B1 would generate the number 2? My second question is - If I have one cell that has a particular date (lodgement date), and then another cell has a completed date and there is a 5 day turn around time - is there a way to calculate how many days it took in a different cell (but not including holidays and weekends?) eg. Lodged 8/8/14 completed 13/8/14 - the cell would generate 4 days. THANKS SO MUCH!!!

 

[VoG]

Try

=IF(A1="DOG",1,IF(A1="CAT",2,""))

and

=NETWORKDAYS(A1,B1)

where A1 = start date and B1 = end date.

 

[jensloth13]

Thankyou

The first one is working but I am still having issues with the second The formula does work except when nothing is the END DATE cell, it brings up a huge figure? Is there another formula I can use that when cell B1 is empt that it just leaves it blank?

I have:

In C6 is a lodgement date of 18/8/14, G6 would be the end date (entered when applicable) and then H6 is the cell that I need to advise how many days are remaining... and this is all based on designated time frames that are in L6 (in this case 10 days)).... so if nothing is entered into G6 its currently giving me a figure of -29899?? When I need it to remain blank.


Thankyou =)

 

[Michael M]

Try

=IF(OR(G6=0,G6=""),"",NETWORKDAYS(C6,G6))

 

[jensloth13]

you guys are the best, thanks so much!