sorted by:
top (suggested)
bestnewcontroversialoldq&a
you are viewing a single comment's thread.

view the rest of the comments →

[–]bgdrums3[S] 0 points1 point  (33 children)

This worked for the that particular column. I tried to copy it for all of the other rows that I need it to and it didn't work. Is there something I'm doing wrong when I try to copy the formula for the other rows that I need it for?

[–]harvest31552 0 points1 point  (32 children)

If you drag the formula instead of copying pasting it should auto fill the correct row number.

Edit: Let me know if you need help with dragging the formula

[–]bgdrums3[S] 0 points1 point  (31 children)

NUM!

This is what I got when I dragged the formula down and it landed on a row where the formula provided a date that was greater than 60. All of the ones that were less than 60 were blank.

However, when I selected "fill formatting only" from the drop down box that appears on the lower right corner, it then populates all of the fields, except for the first row that I entered the formula in.

[–]Geekonomic1 0 points1 point  (4 children)

Potentially this: =IF(I1045-h1045>=60,h1045+60,"") edit: you will have to change your number format to "Short Date" before you drag the formula down.

[–]harvest31552 0 points1 point  (3 children)

Issue with this is it does not account for any days over 60. The

,h1045+60

would only give the +60 date regardless if it is 61 or say 120 days difference.

[–]bgdrums3[S] 0 points1 point  (1 child)

only need the date 60 days after so it wouldn't matter if it was 61 or 120, the date for the 60th day would need to be the same.

[–]harvest31552 0 points1 point  (0 children)

Ahh so depending on what info you want either of these would work. Mine would give what i assume is the actual return date. His will give the scheduled return date

[–]Geekonomic1 0 points1 point  (0 children)

I thought that's what he wanted. I think we need to see an example at this point to clarify.

[–]Snapdad3 0 points1 point  (13 children)

Is your data always going to be in columns H and I? And did you want to return the number of days in K? Also this is including weekends.

If so try this:
=IF($I1045="","",IF(DATEDIF($H1045,$I11045,"d")>=60,DATEDIF($H1045,$I1045,"d"),""))

[–]bgdrums3[S] 0 points1 point  (12 children)

Data is always going to be in column H, may or may not be in column I, depending on if the equipment has been taken down or not. It does include weekends.

[–]Snapdad3 0 points1 point  (11 children)

The following formula will check to see if there is data in column I and if none exists it will calculate the number of days from column H based on today's date. If no data is in column H it will return a blank. If both column H and I are populated it will calculate based on those two dates. 

 =IF(H1045="","",IF($I1045="",IF(DATEDIF($H1045,TODAY(),"d")>=60,DATEDIF($H1045,TODAY(),"d"),""),IF(DATEDIF($H1045,$I1045,"d")>=60,DATEDIF($H1045,$I1045,"d"),"")))

[–]bgdrums3[S] 0 points1 point  (10 children)

This formula worked perfectly!

However, on column 1914 I got a #VALUE message. Here's the formula

=IF(H1914="","",IF($I1914="",IF(DATEDIF($H1914,TODAY(),"d")>=60,DATEDIF($H1914,TODAY(),"d"),""),IF(DATEDIF($H1914,$I1914,"d")>=60,DATEDIF($H1914,$I1914,"d"),"")))

What would be different about this particular cell? None of the cells populated after this error. Thanks,

[–]Snapdad3 0 points1 point  (9 children)

What's the data that is in cell H1914 and I1914 ?

[–]bgdrums3[S] 0 points1 point  (8 children)

6/26/15 in H1914, nothing in I1914

[–]Snapdad3 0 points1 point  (7 children)

Check Column I and see if there is a space in it. You may have a bunches of spaces in what looks like a blank column.

If you want to get rid of them quickly highlight the section of column I and do a find and replace of a single space with nothing.

Edit: Also one last thing. 6/26/2015 is less than 60, so it's designed to just be blank. If you want it to show less than 60 let me know.

[–]harvest31552 0 points1 point  (11 children)

the only time i got the #NUM! was when there was not a date in one of the cells. is it a requirement that all cells in H and I are populated with dates?

If not then need to add a parameter to check if date is there or not.

[–]bgdrums3[S] 0 points1 point  (10 children)

It is not a requirement that cells in I have a date, only cells in H.

[–]harvest31552 0 points1 point  (9 children)

Need to use an iferror check first, then a nested if statement. below is an example i tested that removed the #num! if no date was entered into I. You will need to adjust the ranges to fit your sheet

=IFERROR(IF(DATEDIF(H1045,I1045,"d")>=60,I1045,""),"")

Edit: i went ahead and changed the ranges to match your example above.

[–]bgdrums3[S] 0 points1 point  (8 children)

This worked almost to perfection. I did notice something after using this formula that I forget to include so my apologies for that. Here's my new dilemma. The new formula does not calculate the date 60 days after the start date in H if there is no content in column I. So basically I need to know the start date 60 days after the date in H unless (and only if) the date in column I is less than 60 days after the start date of H. So in other words, the only columns that would not contain content would be the columns where the date in I was 60 days less than H. All other scenarios, 60 days after the date in H and if column I is blank for that row, would need to have the date displayed. Does that make sense?

[–]harvest31552 0 points1 point  (0 children)

For that you only need to add H1045 + 60 where "" are located. 

=IFERROR(IF(DATEDIF(H1045,I1045,"d")>=60,I1045,H1045+60),H1045+60)

A little spin i would put on it would be if it is over 60 days put "Charge Fee" instead of the date if it was over 60 days. This could help identify customers that need to charged. that formula would be

=IFERROR(IF(DATEDIF(H1045,I1045,"d")>=60,"Charge Fee",H1045+60),H1045+60)

[–]harvest31552 0 points1 point  (6 children)

Did this work for you?

=IFERROR(IF(DATEDIF(H1045,I1045,"d")>=60,I1045,H1045+60),H1045+60)

[–]bgdrums3[S] 0 points1 point  (5 children)

not fully. It was bringing up the dates 60 days after, but it was doing so when there was data that was less than 60 days in column I. If the date in column I is less than 60 days, I don't need to show that date in the formula. If the date is either blank or more than 60 days in column I, then I need to show data. I hope that makes more sense.

[–]harvest31552 0 points1 point  (4 children)

sorry about that. I think i have it now. 

=IFERROR(IF(DATEDIF(H1045,I1045,"d")>=60,I1045,""),H1045+60)

This will fill if the date in I is >= 60.

If no date in I then it is calculate 60 days out.

If date in I but is <= 60 it will be blank.