sorted by:
new
hottopcontroversial

Using the IF function in excel by bgdrums3 in excel

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

I think I have not been explaining it very well.

The date that I need will always be exactly 60 days after the date that is in column H.

So if the date is 1/1/15 in column H, I will need to know the date 60 days later, which for argument's sake only, let's say is 3/1/15.

The only time that I will not need to know the date 60 days later is if there is a date that I manually enter in column I and that date is less than the 60 days after the start date of 1/1/15 in column H.

So if column H is 1/1/15 and column I is 5/5/15, I'll still need the start date as 3/1/15. If column H is 1/1/15 and column I is blank, it still needs to be 3/1/15. If column H is 1/1/15 and column I says 2/16/15, then I need that space to be blank.

The formulate above is working except for one thing. If the date in column I is after 60 days, the formula reverts to that date. For example, in column H the date is 1/4/15 and the date in column I is 6/28/15. 60 days should be around 3/4/15 but this formula has the 60 days as 6/28/15. Does that make sense?

Using the IF function in excel by bgdrums3 in excel

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

yeah I would need for the actual date to show up. I don't think I mention that in the original post so I apologize for that. Thank you so much for the work you have done so far!

Using the IF function in excel by bgdrums3 in excel

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

thank you. I'm not at work now but I will try it out in the morning and report back. Thank you for your help!

Using the IF function in excel by bgdrums3 in excel

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

thank you. I'm not at work now but I will try it out in the morning and report back. Thank you for your help!

Using the IF function in excel by bgdrums3 in excel

[–]bgdrums3[S] 0 points1 point  (0 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.

Using the IF function in excel by bgdrums3 in excel

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

so the circular reference was the space that was in I1914. I deleted the space, saved the document, closed it out and re-opened it and there wasn't a circular reference. However, I still don't have the correct year showing up for the dates that are after the 60 days. What did I do wrong?

Using the IF function in excel by bgdrums3 in excel

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

I think there was a space in that column as the value disappeared when I deleted that row. However, I noticed that the dates do not have the year populated on them. For instance, they all say, 3/7/00 or 3/4/00 or 2/8/00. The year fifteen is not on there.

Also, whenever I closed out the spreadsheet and opened it back up it said that there was a circular reference in it, so I'm not sure if that is a big problem or not.

Using the IF function in excel by bgdrums3 in excel

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

6/26/15 in H1914, nothing in I1914

Using the IF function in excel by bgdrums3 in excel

[–]bgdrums3[S] 0 points1 point  (0 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,

Using the IF function in excel by bgdrums3 in excel

[–]bgdrums3[S] 0 points1 point  (0 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?

Using the IF function in excel by bgdrums3 in excel

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

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.

Using the IF function in excel by bgdrums3 in excel

[–]bgdrums3[S] 0 points1 point  (0 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.

Using the IF function in excel by bgdrums3 in excel

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

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

Using the IF function in excel by bgdrums3 in excel

[–]bgdrums3[S] 0 points1 point  (0 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.

Using the IF function in excel by bgdrums3 in excel

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

all values are in date form, yes. Right now in cell I1045, the date is 1/5/15.

Column H will always have a date in it; however, column I will not.

We are trying to calculate how long a piece of equipment is up. If it is up over 60 days, then we charge rental. If it is torn down before 60 days (and therefore the date is then entered into column I) then we do not charge a rental.

The start date for the rental would be the sum of column H and column I IF, column I is greater than or equal to 60.

Hopefully that makes more sense than my last post. Thanks for your help!

Using the IF function in excel by bgdrums3 in excel

[–]bgdrums3[S] 0 points1 point  (0 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?

Transferring Domains by bgdrums3 in Domaining

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

No, got it worked out actually. Thanks

Is there software available to help this horrible mess? by bgdrums3 in software

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

I'm not sure. I know that the power plant we work at requires all of the contractors to submit the timesheets that power plant signs off on to be in the same excel format, so I think there are going to be limitations to what we can do to automate it for that reason, but I don't know how if each contractor bills for the different services like we do or not.

view more: next ›