Hi,

I am making a report in which I want the time differance between two dates.
Eg: In A1 the value is 19/01/2007 16:00:00
In B1 the value is 22/01/2007 11:00:00

A1 is responded time for an issue and B1 is resolved time.
I want the result in C1 as the time differance i.e. B1-A1. When I refered
the posts here, the formula =B1-A1 works only with MMDDYY format and does not
work with my time format which is DDMMYY.

I also want a formula to make excemption of holidays and off hours, say 20th
and 21st Jan are holidays and working hours are from 9:00 to 18:00.

Thanks,
Sujith

=B1-A1 should work OK irrespective of the date format but if you want to
calculate business hours only, assuming A1 and B1 are both within business
hours (by your definition) then try

=(NETWORKDAYS(A1,B1,holidays)-1)*("18:00"-"09:00")+MOD(B1,1)-MOD(A1,1)

format as [h]:mm

note: NETWORKDAYS is part of Analysis ToolPak add-in, if you don't have this
installed use TOOLS Addins and tick "Analysis ToolPak" checkbox

holidays should be a named range including a list of your holiday dates

"Sujith" wrote:

Hi,

I am making a report in which I want the time differance between two dates.
Eg: In A1 the value is 19/01/2007 16:00:00
In B1 the value is 22/01/2007 11:00:00

A1 is responded time for an issue and B1 is resolved time.
I want the result in C1 as the time differance i.e. B1-A1. When I refered
the posts here, the formula =B1-A1 works only with MMDDYY format and does not
work with my time format which is DDMMYY.

I also want a formula to make excemption of holidays and off hours, say 20th
and 21st Jan are holidays and working hours are from 9:00 to 18:00.

Thanks,
Sujith