dates and times in excel
 

3/6Hello.

I have a formula that calculates date and time differences. The formula
works when the same day in inputted in the two fields, but not with two
different days (returns #NAME? . I am using the following format to input
information (yyyy/mm/dd hh:mm) with my present formatting is comes out as
(3/6/06 1:30 PM). the formula I am working with. My DayEnd and DayStart is
8:00 AM - 8:00 PM.
Any ideas?

=IF(AND(INT(I7)=INT(J7),NOT(ISNA(MATCH(INT(I7),Hol idayList,0)))),0,ABS(IF(INT(I7)=INT(J7),ROUND(24*( J7-I7),2),
(24*(DayEnd-DayStart)*
(MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+
INT(24*(((J7-INT(J7))-
(I7-INT(I7)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+
MOD(ROUND(((24*(J7-INT(J7)))-24*DayStart)+
(24*DayEnd-(24*(I7-INT(I7)))),2),
ROUND((24*(DayEnd-DayStart)),2))))))

dates and times in excel
 

http://www.cpearson.com/excel/DateTimeWS.htm#WorkHours

--
Regards,
Tom Ogilvy

"marquis de montrose" wrote in
message ...
3/6Hello.

I have a formula that calculates date and time differences. The formula
works when the same day in inputted in the two fields, but not with two
different days (returns #NAME? . I am using the following format to input
information (yyyy/mm/dd hh:mm) with my present formatting is comes out as
(3/6/06 1:30 PM). the formula I am working with. My DayEnd and DayStart is
8:00 AM - 8:00 PM.
Any ideas?


=IF(AND(INT(I7)=INT(J7),NOT(ISNA(MATCH(INT(I7),Hol idayList,0)))),0,ABS(IF(IN
T(I7)=INT(J7),ROUND(24*(J7-I7),2),
(24*(DayEnd-DayStart)*
(MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+
INT(24*(((J7-INT(J7))-
(I7-INT(I7)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+
MOD(ROUND(((24*(J7-INT(J7)))-24*DayStart)+
(24*DayEnd-(24*(I7-INT(I7)))),2),
ROUND((24*(DayEnd-DayStart)),2))))))

dates and times in excel
 

That looks like a formula from my site. Do you have all the
required names defined?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"marquis de montrose"
wrote in message
...
3/6Hello.

I have a formula that calculates date and time differences. The
formula
works when the same day in inputted in the two fields, but not
with two
different days (returns #NAME? . I am using the following
format to input
information (yyyy/mm/dd hh:mm) with my present formatting is
comes out as
(3/6/06 1:30 PM). the formula I am working with. My DayEnd and
DayStart is
8:00 AM - 8:00 PM.
Any ideas?

=IF(AND(INT(I7)=INT(J7),NOT(ISNA(MATCH(INT(I7),Hol idayList,0)))),0,ABS(IF(INT(I7)=INT(J7),ROUND(24*( J7-I7),2),
(24*(DayEnd-DayStart)*
(MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+
INT(24*(((J7-INT(J7))-
(I7-INT(I7)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+
MOD(ROUND(((24*(J7-INT(J7)))-24*DayStart)+
(24*DayEnd-(24*(I7-INT(I7)))),2),
ROUND((24*(DayEnd-DayStart)),2))))))

dates and times in excel
 

Hello Chip.

It did come from your site. As a matter of fact, your site has taught me a
lot about this functionality, and I thank you for that. Yes. I beleive that
all of my required names are defined. Would it come out with an answer like
that if they were not, being that it works for the same day but not two
different days. I will check the names again and make sure that this is not
the case. I guess I am trying to rule out things like formatting that may
affect this. I will get back to you tomorrow about the names. Thanks again.

"Chip Pearson" wrote:

That looks like a formula from my site. Do you have all the
required names defined?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"marquis de montrose"
wrote in message
...
3/6Hello.

I have a formula that calculates date and time differences. The
formula
works when the same day in inputted in the two fields, but not
with two
different days (returns #NAME? . I am using the following
format to input
information (yyyy/mm/dd hh:mm) with my present formatting is
comes out as
(3/6/06 1:30 PM). the formula I am working with. My DayEnd and
DayStart is
8:00 AM - 8:00 PM.
Any ideas?

=IF(AND(INT(I7)=INT(J7),NOT(ISNA(MATCH(INT(I7),Hol idayList,0)))),0,ABS(IF(INT(I7)=INT(J7),ROUND(24*( J7-I7),2),
(24*(DayEnd-DayStart)*
(MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+
INT(24*(((J7-INT(J7))-
(I7-INT(I7)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+
MOD(ROUND(((24*(J7-INT(J7)))-24*DayStart)+
(24*DayEnd-(24*(I7-INT(I7)))),2),
ROUND((24*(DayEnd-DayStart)),2))))))

dates and times in excel
 

I will add. I don't think that the names for StartDT and EndDT are the proper
case. I will get back to you.

"Chip Pearson" wrote:

That looks like a formula from my site. Do you have all the
required names defined?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"marquis de montrose"
wrote in message
...
3/6Hello.

I have a formula that calculates date and time differences. The
formula
works when the same day in inputted in the two fields, but not
with two
different days (returns #NAME? . I am using the following
format to input
information (yyyy/mm/dd hh:mm) with my present formatting is
comes out as
(3/6/06 1:30 PM). the formula I am working with. My DayEnd and
DayStart is
8:00 AM - 8:00 PM.
Any ideas?

=IF(AND(INT(I7)=INT(J7),NOT(ISNA(MATCH(INT(I7),Hol idayList,0)))),0,ABS(IF(INT(I7)=INT(J7),ROUND(24*( J7-I7),2),
(24*(DayEnd-DayStart)*
(MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+
INT(24*(((J7-INT(J7))-
(I7-INT(I7)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+
MOD(ROUND(((24*(J7-INT(J7)))-24*DayStart)+
(24*DayEnd-(24*(I7-INT(I7)))),2),
ROUND((24*(DayEnd-DayStart)),2))))))

dates and times in excel
 

You may be able to use an easier formula.

Can your start and end times/dates be outside working hours?

If not then you could use

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

custom format as [h]"hrs" m"mins"

where A1 contains start time/date, B1 end time/date and holidays is a
named range containing holiday dates.


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=519526

dates and times in excel
 

Hi Chip.

I apologize for my lack of e-mail etiquette. I should have posted your
website along with my question. I have yet to really sit down and be able to
analyze all of the names I created. I will keep you posted as to my success
(if you wish). Thanks again.

"Chip Pearson" wrote:

That looks like a formula from my site. Do you have all the
required names defined?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"marquis de montrose"
wrote in message
...
3/6Hello.

I have a formula that calculates date and time differences. The
formula
works when the same day in inputted in the two fields, but not
with two
different days (returns #NAME? . I am using the following
format to input
information (yyyy/mm/dd hh:mm) with my present formatting is
comes out as
(3/6/06 1:30 PM). the formula I am working with. My DayEnd and
DayStart is
8:00 AM - 8:00 PM.
Any ideas?

=IF(AND(INT(I7)=INT(J7),NOT(ISNA(MATCH(INT(I7),Hol idayList,0)))),0,ABS(IF(INT(I7)=INT(J7),ROUND(24*( J7-I7),2),
(24*(DayEnd-DayStart)*
(MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+
INT(24*(((J7-INT(J7))-
(I7-INT(I7)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+
MOD(ROUND(((24*(J7-INT(J7)))-24*DayStart)+
(24*DayEnd-(24*(I7-INT(I7)))),2),
ROUND((24*(DayEnd-DayStart)),2))))))