dates and times in excel

[marquis de montrose]

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),HolidayList,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))))))

[Tom Ogilvy]

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

--
Regards,
Tom Ogilvy

"marquis de montrose" <[email protected]> wrote in
message news:[email protected]...
> 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),HolidayList,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))))))

[Chip Pearson]

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"
<[email protected]> wrote in message
news:[email protected]...
> 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),HolidayList,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))))))

[marquis de montrose]

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"
> <[email protected]> wrote in message
> news:[email protected]...
> > 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),HolidayList,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))))))
>
>
>

[marquis de montrose]

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"
> <[email protected]> wrote in message
> news:[email protected]...
> > 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),HolidayList,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))))))
>
>
>

[daddylonglegs]

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.

[marquis de montrose]

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"
> <[email protected]> wrote in message
> news:[email protected]...
> > 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),HolidayList,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))))))
>
>
>