Using Dates In Excel Spreadsheets – It Can Be Tricky

Excel spreadsheets (.xls)

The other day, I got a question about using dates in excel spreadsheets. It’s very tricky to use both dates and numbers in Excel and certainly not as easy as it should be. Before getting started, I would love to invite you to ask us any questions that you might have. You can simply go to our contact page.

Back to our spreadsheet, many users email me every month with questions regarding dates in excel. The difficult part about writing posts is that almost every problem is a bit different compared to vlookup or sumif issues which are almost all similar. Today’s issue is a rather common one. First let’s take a look at the spreadsheet:

The objective is to fill out the last 2 columns. First off, we determine if the task is overdue. It’s quite simple to find out if it is, as we have today’s date. If the due date is further than today’s date, the number should be over 0. However, we cannot have “negative days”. Therefore, I will be using this function:

=MAX($D$3-E6,0)

Then, the status should be either “Outstanding” or “Closed”. In this case, Outstanding means:

-There has not been a “response date”
-Overdue days are over 0

I will use the “Nested Excel And function” that I presented some time ago. Here is the formula that I will use:

=IF(AND(G6>0,F6=””),”Outstanding”,”Closed”)

I also added conditional formatting to make it look even more obvious when issues were “Outstanding”. This is what it looks like:

I also invite you to download the spreadsheet for additional information here:)

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss


***************************************************

5 Feedbacks on "Using Dates In Excel Spreadsheets – It Can Be Tricky"

Pankaj aggarwal

the using date in excel spreadsheet little bit confusing concept can u make me understand in detail. in addition this is quite helpful for me



krunal naik

pls send me the demo of simple way to use vlook up formula in excle



Ian Chivers

I am using excel for my personal cash flow and want to use an ‘if’ function for my SO’s & DD’s which don’t change. But I only insert dates as I spend or enter wages etc and use =if(day(b3)=day($c30),$b30,””). So what I would like is if the date is the 2nd and the next date I put in is the 4th then it uses that date value as it is the next day of a greater date. I have got an example spreadsheet that I could send if you want to see what I’ve tried, where I’ve tried modifying the above formula.



Claudine Murray

Hello, I would like some help with a spreadsheet I created for work. I have row A filled across with dates. I have row B filled in with a .5 or 1 if an employee was out of the office for a 1/2 day or full day. In row B, some cells are filled in and some are not. I would like to get a list of dates with a number below it combined into one cell.



kavyashree.S

pls help me in understanding vlookup !!