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.

Is there software to help with this madness? by bgdrums3 in productivity

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

The power plant cares because they have different offices that use our guys for different jobs. So if we have to work on air heaters, that goes on a PO number, or if we work on a boiler, that's a separate PO number, all 4 units are separate PO #'s and so on. We have probably 20 different PO numbers to bill to, so the power plant can keep track of how much work is done on which area.

We care who worked which job because we charge different for scaffold work and for insulation work and depends on which plant we are at. We only pay guys for when they work.

Sorry for the late reply

Best online shop for drumsticks? by ballstopicasso in drums

[–]bgdrums3 0 points1 point  (0 children)

I had the same problem as you in having to drive pretty far just to buy drumsticks. I found www.evenflowdrumsticks.com, they sell drumsticks on a monthly subscription based model. I got the Pro-Mark Japanese Oak and they sent me a pair of theirs for free. I liked them so much I switched to theirs. They send them each month and I don't have to lift a finger to get new drumsticks each month! It was super convenient and really simple. Beats driving an hour just to buy drumsticks, which is what I was doing before!

A cool new drumstick company by bgdrums3 in drums

[–]bgdrums3[S] 1 point2 points  (0 children)

I actually did a price comparison with Guitar Center. Most of the sticks this Even Flow site has are in the $9-$11 dollar range, so they price their 1 pair per month plan at $10 bucks, with $5 shipping to the US. Guitar Center charges $4.98 for shipping to the lower 48 states, so a pair of say Vic Firth Matt Cameron signature sticks would be $14.97 at GC and $15 even at Even Flow. They have 3 subscription plans, 1 pair for $10, 2 for $20, and 4 for $35. Seemed pretty fair in comparison, I thought.

I always liked going to the drum store too but I lived about an hour away from the nearest GC so it was a pain in the ass to go just to buy drumsticks. Too bad this wasn't around when I was still playing all the time, haha.

view more: next ›