sum month

[LJ]

I am working in the building and have to calculate the time span each tenant
stayed . I have "date in" and "date out" and "time spent" columuns. Does
anyone can give the formula for "time spent" column?

Thanks!

LJ

 

[JulieD]

Hi

assume
Date in is cell B2
Date out is cell C2
formula for D2
=C2-B2
format as number to get the number of days that the tenant stayed.

Cheers
JulieD

 

[JE McGimpsey]

Alternatively, if you want the "time spent" in other units, check out
the DATEDIF function:

http://cpearson.com/excel/datedif.htm

 

[L.J.]

Thanks you for pointing out the resouces.
However I still have a difficult to calculate the time interval.
This is what I have do
column A column B
column C

1
Date in
Date out
interval

2
Jul-03 (mmm,yy)
Aug-04
=datedif(B2,A2,"m")

3
Jan-04
Sep-04




The result is "#NUM!", how should I format cell C2?
Is there any step I went wrong?

Your help would be much appreciated.

LJ

 

[L.J.]

Sorry, I have to send it again, because it does not appear what I posted
originally.

1. In cell B2, I entered Jul-03 (cell is formatted mmm,yy) as Date In
2. In cell C2, I entered Jun-04 (cell is formatted mmm,yy) as Date Out
3. D2 is for "interval time" : I used formula --> datedif (C2,B2,"m")
And the result I got is "#NUM!".

How should I format Cell D2? Which step I went wrong?
Any help would be much appreciated.

LJ

 

[JulieD]

Hi

the formula you use should be
=datedif(b2,c2,"m")

the syntax is
=datedif(startdate, enddate,timeunit)

Cheers
JulieD

 

[L.J.]

Thanks Julie. One more question year, if the period of spent time is 13
months, is there any syntax can show 1 year 1 month?

Regards,
LJ

 

[JulieD]

Hi LJ

yes you can - Chip Pearson has a good example of how to do this, (using an
Age calculation but it should give you an idea) on his web page about using
datedif at

http://www.cpearson.com/excel/datedif.htm#Age

it's using the concatenation operant of & e.g.
if A1 was John and B1 was Smith
=A1 & " " & B1
would return
John Smith

Regards
Julie Dall