Day Calculation from any date to any date

Hi,

I have my passport stamped in and stamped out date for the last 3 years, and I want to know how many days was I in country or out of country from any date to any date.
For example, my query will be from 1st july to 31st june, how many days was I out of country.
I am attaching the excel sheet.
Thanks in advance for the help.

Put this in D3:

=IF(OR(B3="",C3=""),0,C3-B3)

Format the cell as General or as Number, then copy down.

Hope this helps.

Pete

Dates in excel are stored as numbers so you can simple substract one date from another as long as it does not result in negative numbers

so a simple =C3-B3 will result in 92 days.

see also attached sheet.

just as FYI: time are fractions of 1 so if stamps would also have a time you could even calculate the days and hours spend in a country.

Thanks for the help, Pete.
I need to get this info from a particular date to a particular date, and not just the days in between.
Please, I want to put in 2 dates, and need the calculation of days in country between those dates.
Hope I made myself clearer now.
Sudeepta

So you want to know the days between stamp in 13-4-2016(cell C3) and 26-6-2016 (Cell B4)?

that is simular only just not take the dates from the same line.. To get a good result always start with newest date. so in this case =B4-C3 will get you 74 days in India. you may want to add +1 if you want to count the day in as day in the country.

Hi Roel,

I want days between random dates ex. how many days was I out from 1/1/2017 to 31/7/2017?

Thanks

I'm sorry, now you lost me.. what is the purpose of a table example? how are the random dates determined then?
the calculation is very easy as you can see. substract oldest date from the newest and you get days.

I really do not get what you mean by days between random dates..

The dates are actual dates of arrival/departure, and I want to generate year wise breakup of how many days in country.

If you are only interested in whole years, use D1 to record the year of interest (e.g. 2017), then you can use this formula in cell D3:

=IF(OR(B3="",C3=""),0,IF(OR(C3 < DATE(D$1,1,1),B3 > DATE(D$1+1,1,0)),0,IF(AND(B3 < =DATE(D$1+1,1,1),C3 > =DATE(D$1+1,1,1)),DATE(D$1+1,1,1),C3)-IF(AND(B3 < DATE(D$1,1,1),C3 > =DATE(D$1,1,1)),DATE(D$1,1,1),B3)))

Copy down as required.

Note that I have had to introduce spaces around < and > to get past the firewall.

Hope this helps.

Pete

Thanks a lot Pete, unfortunately, this did not work even as I removed the spaces as you said.
Is there any way I can have 2 cells to put in from day and to day, and then the calculation is done.
Hope I get a solution to this soon,
Thanks

Hi Pete,
Thanks for the effort you are putting in, your answer helped me frame the question better and clearer, I guess.
Please see the attached, I want excel to calculate me this, I know you can do it.
Thanks.

Well, I might be able to do it, but I'm just about to go out now, so I'll take a more detailed look later on when I get back.

Pete

Sure,
shall be waiting for your reply.
Thanks.

Try this:

One horrendous IF statement:

Formula:

Please Login or Register  to view this content.

See the attached workbook

Hi Pete,

Thanks a lot!! Looks like this Horrendous "IF" statement is indeed working. There is a small challenge with the days in India, but that is minor one.
Shall get back once I go through the same and use it a couple of times.
Did I mention You are GOOD at this?

Best regards

That last one was from Geoff, but it does the same job as mine from Post #11, although in a different way.

Pete