Help with advanced date calculations in excel.?

I'd like a cell in Excel to calculate "difference of two date cells" when the two cells have data entered. Plus, the cell should also be calculating from start date up to today's date(reflecting number of days so far) and should stop when stop date has been entered. Doing this, I should be able to know how many days have been consumed so far. It should also stop counting when the stop date has been entered. I am building an excel spreadsheet for recording working days. The employee can add a START and END date for each transaction. I would like the days cell to start calculating when the START date is entered reflecting how many days consumed up to date, and stop calculating when the END date has been entered. Is it possible? Any answers are highly appreciated for I am in a big mess tracking numerous transactions





Answers (1)





Assuming the start date is in A1 and the end date in B1





=NETWORKDAYS(A1,IF(B1="", TODAY(),B1))





The above formula uses the NETWORKDAYS function, with does not count Saturdays and Sundays (and any holidays you want to include). You might have to format the cell as a number.


The above answer is exactly what I was looking for. However, my problem still remains that when I add a new row to my table, the calculation automatically takes place giving me a very large number that I don't want. Formula should only start calculating when the start date is entered. Thanks in advance.|||If I understand your question correctly


Can see no reason why you cannot do a similar check on A1 as you already did on B1


so formula becomes


=IF(A1="","",NETWORKDAYS(A1,


IF(B1="", TODAY(),B1)))





HTH