Help Me!!!!

[wcmash]

Can anyone help I need to know how to find out if a certain weekday fall
between 2 dates.

 

[Frank Kabel]

Hi
not quite sure what you're trying
can you give an example?

 

[Norman Harker]

Hi wcmash!

Here's an excerpt from Chip Pearson's page on Weekday calculations:

Extract>>>
If you need to return the number of Monday's (or any other day) that
occur within an interval between two dates, use the following Array
Formula:

=SUM(IF(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2,1,0))

This formula assumes the following:
A2 contains the beginning date of the interval
B2 contains the ending date of the interval
C2 contains the day-of-week number (1=Sunday, 2=Monday,...,7=Saturday)

<<End Extract

Note especially that this is an array formula entered by pressing and
holding down Ctrl and Shift and then pressing Enter.

For more Weekday formulas see:

http://www.cpearson.com/excel/DateTimeWS.htm
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[wcmash]

Okay this is the problem my company has 48 hour turnarounf time So lets
say the beggining date is 3/12/04 4:00p.m which is a friday and the
ending date is 3/16/04 which is a tuesday. I need to know how many
hours are between the 2 dates but without the weekends.

 

[Frank Kabel]

Hi
do you also have specific business hours. that is count only houer
between 08:00 and 17:00 for a weekday??

 

[Norman Harker]

Hi wcmash!

I should have gone on to say, that to find out if there is a
particular day in the period use:

=IF(SUM(IF(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2,1,0))<>
0,TRUE,FALSE)

As an example:
A2:
23-Feb-2004
B2:
27-Feb-2004
C2:
2 [Weekday number for Monday]
D2:
=IF(SUM(IF(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2,1,0))<>
0,TRUE,FALSE)
Returns: TRUE because 23-Feb-2004 was a Monday.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Hi wcmash!

Here's an excerpt from Chip Pearson's page on Weekday calculations:

Extract>>>
If you need to return the number of Monday's (or any other day) that
occur within an interval between two dates, use the following Array
Formula:

=SUM(IF(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2,1,0))

This formula assumes the following:
A2 contains the beginning date of the interval
B2 contains the ending date of the interval
C2 contains the day-of-week number (1=Sunday, 2=Monday,...,7=Saturday)

<<End Extract

Note especially that this is an array formula entered by pressing and
holding down Ctrl and Shift and then pressing Enter.

For more Weekday formulas see:

http://www.cpearson.com/excel/DateTimeWS.htm
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Can anyone help I need to know how to find out if a certain

weekday

 

[Norman Harker]

Hi Frank!

Looks like you were wise to ask for more details
Think I'll wait for the answers to these follow throughs.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Hi
do you also have specific business hours. that is count only houer
between 08:00 and 17:00 for a weekday??

 

[Frank Kabel]

Hi Frank!

Looks like you were wise to ask for more details
Think I'll wait for the answers to these follow throughs.

Norman
just a little bit lazy today
and after seeing your formulas I assumed better to wait as the formulas
probably will get more complicated (as the requirements will definetly
grow...)

Best regards
Frank

 

[wcmash]

It is a transcription service that works 24 hours a day except saturda
and sunday

 

[Norman Harker]

Hi wcmash!

Try the following:

=((NETWORKDAYS(A2,B2)-2)+(1-MOD(A2,1))+MOD(B2,1))*24
Format General

Or

=(NETWORKDAYS(A2,B2)-2)+(1-MOD(A2,1))+MOD(B2,1)
Format [hh]:mm

Example:

Start time
A2: 20-Feb-2004 4:00:00 PM
End Time
B2: 27/02/2004 2:00:00 PM

=NETWORKDAYS(A2,B2) returns the number of whole working days inclusive
of start day and end day. So deducting 2 gives the whole working days
exclusive of those days. (1-MOD(A2,1) gives the decimal part of a day
worked on the start day and MOD(B2,1) gives the decimal part of a day
worked on the end day.



--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.