I want to calculate the number of days between the date in column F and E and G and E, and report the larger number in column H. I want to have column H stay blank if either column F or G is blank. So far, I can't figure out how to add " if column G is blank" to my formula without getting an error code.
E F G H
1 4/15/13 4/25/13 4/30/13 15
2 4/1/13 4/25/13
3 4/15/13 4/20/13
=IF(F1="","", MAX((F1-E1),(G1-E1)))
Thank you Excel wizards for your help!
Hi and welcome to the forum
Try this...
=IF(or(F1="",G1=""),"", MAX(F1-E1,G1-E1))
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Oh my gosh. You are so awesome. Thank you for responding so fast. I have anther question for you. If I allow n/a to be put in the date column, can I then allow the calculation? by way of explanation, one column is for the patient and the other for the partner and if there is no partner, I want the person entering the data to type in n/a and still have the calculation done. Am I making sense?
Thanks again for your help. Can't believe how long I have been working on this and you fixed it in a flash for me!
Last edited by JulesTruly; 04-19-2013 at 11:41 PM.
Thanks for the kind words
If I understand you correctly, try this. You may need to swap the ranges around if I have them mixed up...
=IF(or(F1="",G1=""),"", if(F1=0,G1-E1,MAX(F1-E1,G1-E1))
or
=IF(or(F1="",G1=""),"", if(G1=0,F1-E1,MAX(F1-E1,G1-E1))
Now I am getting a "VALUE! error.
OK maybe Im missing something. can you upload a sample workbook please?
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"
Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.
Thank you very much for the detailed instructions. I have attached the file.
oops OK my bad, I used "0" instead of n/a...but your files showed me that you actually want the test both ways, so...
=IF(OR(F2="",G2=""),"",IF(F2="n/a",G2-E2,IF(G2="n/a",F2-E2,MAX(F2-E2,G2-E2))))
Also, you will need to be careful that only n/a is entered and not na or n.a or n-a etc
I thought I tried changing that but apparently I didn't do that correctly either.
Thank you so much for taking the time to help me. I really appreciate it.
I wish I had found this forum earlier, but I am so happy I know about it now!
Thanks again.
Maybe you did the same as I did, when I 1st changed it, I changed it to na instead of n/a?
Im happy we managed to get your question answered, thanks for the feedback, and thanks for joining the forum
FDibbins, can I ask you another question? I have a column that I have locked so the user can not enter anything in it and the formula remains hidden. It is the one you helped me with the other day
When I password protect the sheet, my user can no longer sort it. Is there a way to lock a column, but also allow for sorting? Hope this makes sense!
Thanks again.
If you allow them to open it as "read-only", they can do whatever they want to the file, but they cannot save it
I was afraid that might be the case. Thanks so much for all your help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks