Help with TODAY function and garbage string

[4trevor]

Hi. I'm new to using functions in a spreadsheet and have figured out how to get a certain function to work for me, but the trouble is this:

excel pic.jpg

I basically have a submission tracking sheet and in the picture the function I've put in is =TODAY() - H11. I have dates in H10 and H9, and the numbers in I9 and
I10 are the number of days between the current date and the date ranges in the 2 cells. I also have some conditional formatting so that if the number of days in column I is less than 90 it displays in red and if more than 90 it displays in green.

The trouble I'm having is that when I haven't put a date into column H this funky string of numbers shows up in column I anyway. I want the cells in this column to be blank until I input a date into column H and force the function to work and calculate the number of days that have passed since the original date in column H and the current date in column I.

But I can't figure it out. Any help is greatly appreciated!

[Kevin UK]

Hi 4trevor

If I read you correctly!

=IF(H?="","",Your Formula)

[4trevor]

Kevin, hey thanks for replying and helping me out. This is what I tried based on your advice:

=IF(H11="","",TODAY() - H11)

Still had string of numbers in target cell H11. Then I tried it slightly different thinking I had put a parenthesis in the wrong place:

=IF(H11="","",TODAY( - H11))

Unfortunately this didn't work either. I'm really at a loss here, I don't understand

[shg]

Maybe

=IF(ISNUMBER(H11), TODAY() - H11, "")

[4trevor]

Hey shg, I tried that and it's giving me error 508. There has to be some way to force the target cell I11 to be blank until a date is put into H11 to force function to calculate number of days between date in H11 and the current date.

I also tried this formula thinking it should work:

=IF(H11="",""(TODAY() - H11))

but I don't know if it just isn't right or if it's not nested correctly. I've been googling IF functions and TODAY functions and trying to merge them together somehow. I don't know what error 508 means

[shg]

Error 508? Not from a formula. Is this VBA?

[4trevor]

Shg, sorry I just realized that I posted on this forum but I'm actually using Open Office. Crap! I guess there's a difference between the function coding in Microsoft and Open Office. Sorry to have wasted your time trying to help me when the coding might be different and what you're advising me to do would work in Microsoft but not in Open Office. I found the Open Office forum and posted the problem there. Maybe if the coding is that much different someone over there might be more familiar with the function structure and be able to figure it out.

Much thanks guys and sorry again!

[4trevor]

Okay I found out what it was. Apparently I needed a semicolon and not a comma to separate the IF function from the TODAY function. Go

[4trevor]

Not sure what happened why my last post was truncated. Anyway this is what worked for me..

=IF(H11="";"";TODAY() - H11)

[Kevin UK]

Hi 4trevor

This formula did work for you then, just that we use comma in our syntax over here.

Please Login or Register  to view this content.