Calculating time between two dates
 

Hi,
Thank you so much for the effort you have put in.
But my badluck, this is not working :(
I am using MS Office 2003. Will there be any differance depends on the
version?

I entered in A1- 19/01/2007 16:00:00
in B1- 22/01/2007 11:00:00
and
in C1- =(NETWORKDAYS(A1,B1,holidays)-1)*("18:00"-"09:00")+MOD(B1,1)-MOD(A1,1)

I get result in C1 as #VALUE!
I have added Analysis ToolPak as mentioned.

Thanks,
Sujith

"daddylonglegs" wrote:

=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

Calculating time between two dates
 

Hello Sujith,

If you get #Value! then that probably means your dates are not being
recognised as dates. You said in your first post that you couldn't get =B1-A1
to work with dates formatted as dd/mm/yy.

If your regional settings (in control panel) have default date set as
mm/dd/yy then if you enter 20/01/2007 this won't be recognised as a date but
as text, hence the error. Either enter your dates in mm/dd/yy format or
change the regional settings to allow entry as dd/mm/yy.

The formula I suggested should then work. Once dates are recognised as such
you can then format them as you want, so even if you had to enter 01/20/2007
you can still format this as dd/mm/yyyy and the cell will actually show
20/01/2007

"Sujith" wrote:

Hi,
Thank you so much for the effort you have put in.
But my badluck, this is not working :(
I am using MS Office 2003. Will there be any differance depends on the
version?

I entered in A1- 19/01/2007 16:00:00
in B1- 22/01/2007 11:00:00
and
in C1- =(NETWORKDAYS(A1,B1,holidays)-1)*("18:00"-"09:00")+MOD(B1,1)-MOD(A1,1)

I get result in C1 as #VALUE!
I have added Analysis ToolPak as mentioned.

Thanks,
Sujith

"daddylonglegs" wrote:

=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