Forum Discussion
Calculating multiple dates in excel
Hello! 🙂
I have spent countless hours trying to manipulate a way to obtain the formula that was utilized in Column F (vendor can not provide it) in order to be able obtain the same charge days in Column G.
So far I have used in Column G : =IFERROR(IF(B2<=$C$2,0,ROUNDUP(IF($B2>"",SUM($E2-$C$2)+1,IF($B2<0,SUM($B2-$C2)+1,"0")),0)),0)
As you can see in rows 8 and 9 in Columns F and G, there is a discrepancy where the formula is not picking up the July dates in Column A.
Lenni099 I tried replicating your situation in a sheet of my own and couldn't figure out how your formula would even work as it makes no sense at all.
The only way I could get to your results was to put text (any text, that is) in column B and that makes me believe that the 'dates' shown in column B are in fact texts that merely look like dates. That's why the formula doesn't pick-up the fact that the IN 'date' is after July 1. The result will always come from SUM($E3-$C$2)+1
Try this instead in E2 and copy down.
=IF(B2="","-",E2-MAX(B2:C2)+1)Thank you for your response and feedback. The formula I originally inserted originally came from an excel macro that we use that does work but in a slightly different situation.
I tried both formulas you provided and they do work , however, even though I changed the dates in Column B to text, any dates in July in Column B, are still not matching with the number I get in Column G vs what the vendor has listed :
Lenni099 Riny is absolutely right that your data in columns B through E should be dates, not text. His formula is partially correct, but what I see is that (Correct) Charge Days is the number of days encompassed by these two dates, inclusive:
- the later of Gate in and INV START DATE
- the earlier of INV END DATE and Gate Out
Therefore, the formula for row 3 should be something similar to:
=MIN($D2,$E2) - MAX($B2,$C2) + 1In the future, please attach a small spreadsheet file (enough to demonstrate the issue at hand; be sure to remove any sensitive data) rather than making us redo the data entry.
