dates and times in excel

  • Thread starter marquis de montrose
  • Start date
M

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))))))
 
C

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"
 
M

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.
 
M

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.
 
D

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.
 
M

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top