maxhayden
05-26-2009, 06:37 AM
Hi everyone!
I have written a user form that adds dates and percentages to cells on a spreadsheet.
I have an If statement in another cell that analyses the values that have been input:
=IF(H7="","",IF(AND(H7<TODAY(),I7<100%),"Overdue",IF(AND(H7=TODAY(),I7<100%),"Due Today",IF(AND(H7=TODAY()+1,I7<100%),"Due Tomorrow",IF(AND(G7<TODAY(),H7>TODAY()),"In Progress",IF(G7>TODAY(),"Not Started",IF(I7=100%,"Complete","Unknown")))))))
Yes! Its ugly alright!!
Anyway!! When I input the dates manually, the formula works fine!
When I input the dates using the user form - it doesn't recognise it as a date and comes up with "Not Started" for any date I put in!!!
Can anyone help me? I don't really want to run the above formula as a macro, because I want it to be "real time" without having to run the macro constantly!
I have written a user form that adds dates and percentages to cells on a spreadsheet.
I have an If statement in another cell that analyses the values that have been input:
=IF(H7="","",IF(AND(H7<TODAY(),I7<100%),"Overdue",IF(AND(H7=TODAY(),I7<100%),"Due Today",IF(AND(H7=TODAY()+1,I7<100%),"Due Tomorrow",IF(AND(G7<TODAY(),H7>TODAY()),"In Progress",IF(G7>TODAY(),"Not Started",IF(I7=100%,"Complete","Unknown")))))))
Yes! Its ugly alright!!
Anyway!! When I input the dates manually, the formula works fine!
When I input the dates using the user form - it doesn't recognise it as a date and comes up with "Not Started" for any date I put in!!!
Can anyone help me? I don't really want to run the above formula as a macro, because I want it to be "real time" without having to run the macro constantly!