Time Formula #2

Hi

I run Excel 2K

I download data from a mainframe. This data has a date & time format in it 
(dd/mm/yyyy hh:mm:ss)

I work in a place that has a 3 shift cycle - day shift, afternoon shift, 
night shift.

Day shift starts at 7:20am and ends at 15:29pm
Afternoon shift starts at 15:30pm and ends at 23:19pm
Night shift starts at 23:20pm amd ends at 7:19am

I need a formula that looks at the cell with the date/time in it and 
displays the word DAY (for the time frame of day shift), AFTERNOON (for the 
timeframe of afternoon shift) and NIGHT (for the timeframe of night shift)

In my previous job I used a formula for a different shift pattern which was 
12 hour one which worked really well however the shift pattern that I need is 
for is an 8 hour one, and I dont know how to edit the formula I used for the 
12 hour pattern.

This is the formula I used for the 12 hour shift pattern:

=IF(AND(TEXT(F12-INT(F12),"hh:mm:ss")>="07:00:00",TEXT(F12-INT(F12),"hh:mm:ss")<"19:00:00"),"Day","Night")


I hope I have managed to explain this ok.

Thanks

John
0
JohnCalder (178)
8/18/2009 12:37:01 AM
excel.newusers 15348 articles. 2 followers. Follow

11 Replies
977 Views

Similar Articles

[PageSpeed] 7

Assuming your dates/times are true Excel dates/times.

One way...

Create a lookup table like this:

...........J..........K
1....0:00.....Night
2....7:20.....Day
3..15:30.....Afternoon
4..23:20.....Night

Then...

A1 = some dd/mm/yyyy hh:mm:ss

=LOOKUP(MOD(A1,1),J$1:K$4)

-- 
Biff
Microsoft Excel MVP


"John Calder" <JohnCalder@discussions.microsoft.com> wrote in message 
news:745C71FA-3509-42AD-B0C4-E5B8E1540E9F@microsoft.com...
> Hi
>
> I run Excel 2K
>
> I download data from a mainframe. This data has a date & time format in it
> (dd/mm/yyyy hh:mm:ss)
>
> I work in a place that has a 3 shift cycle - day shift, afternoon shift,
> night shift.
>
> Day shift starts at 7:20am and ends at 15:29pm
> Afternoon shift starts at 15:30pm and ends at 23:19pm
> Night shift starts at 23:20pm amd ends at 7:19am
>
> I need a formula that looks at the cell with the date/time in it and
> displays the word DAY (for the time frame of day shift), AFTERNOON (for 
> the
> timeframe of afternoon shift) and NIGHT (for the timeframe of night shift)
>
> In my previous job I used a formula for a different shift pattern which 
> was
> 12 hour one which worked really well however the shift pattern that I need 
> is
> for is an 8 hour one, and I dont know how to edit the formula I used for 
> the
> 12 hour pattern.
>
> This is the formula I used for the 12 hour shift pattern:
>
> =IF(AND(TEXT(F12-INT(F12),"hh:mm:ss")>="07:00:00",TEXT(F12-INT(F12),"hh:mm:ss")<"19:00:00"),"Day","Night")
>
>
> I hope I have managed to explain this ok.
>
> Thanks
>
> John 


0
biffinpitt (3172)
8/18/2009 1:06:22 AM
Hi

I quoted the wrong time format (24hr clock) I have edited the original 
question and updated it to the correct format ( 12hr clock)

Cheers

"John Calder" wrote:

> Hi
> 
> I run Excel 2K
> 
> I download data from a mainframe. This data has a date & time format in it 
> (dd/mm/yyyy hh:mm:ss)
> 
> I work in a place that has a 3 shift cycle - day shift, afternoon shift, 
> night shift.
> 
> Day shift starts at 7:20am and ends at 3:29pm
> Afternoon shift starts at 3:30pm and ends at 11:19pm
> Night shift starts at 11:20pm amd ends at 7:19am
> 
> I need a formula that looks at the cell with the date/time in it and 
> displays the word DAY (for the time frame of day shift), AFTERNOON (for the 
> timeframe of afternoon shift) and NIGHT (for the timeframe of night shift)
> 
> In my previous job I used a formula for a different shift pattern which was 
> 12 hour one which worked really well however the shift pattern that I need is 
> for is an 8 hour one, and I dont know how to edit the formula I used for the 
> 12 hour pattern. This wa based on a 24 hr clock time format.
> 
> This is the formula I used for the 12 hour shift pattern:
> 
> =IF(AND(TEXT(F12-INT(F12),"hh:mm:ss")>="07:00:00",TEXT(F12-INT(F12),"hh:mm:ss")<"19:00:00"),"Day","Night")
> 
> 
> I hope I have managed to explain this ok.
> 
> Thanks
> 
> John
0
JohnCalder (178)
8/18/2009 1:12:01 AM
"John Calder" <JohnCalder@discussions.microsoft.com> wrote:
> I need a formula that looks at the cell with the date/time in it and
> displays the word DAY (for the time frame of day shift), AFTERNOON (for 
> the
> timeframe of afternoon shift) and NIGHT (for the timeframe of night shift)

Try:

=LOOKUP(TEXT(MOD(A1,1),"hh:mm:ss"),
      {"00:00:01","07:20:00","15:30:00","23:20:00"},
      {"NIGHT","DAY","AFTERNOON","NIGHT"})


> I quoted the wrong time format (24hr clock) I have edited the original
> question and updated it to the correct format ( 12hr clock)

The format of the cell (A1) does not matter.  The internal form (date/time 
serial number) remains the same.  The TEXT() expression above converts the 
serial number to 24-hour time, which is needed for the proper lookup 
comparison.


----- original message -----

"John Calder" <JohnCalder@discussions.microsoft.com> wrote in message 
news:BE470D46-E409-41B8-BFCA-15C6CC6BC8EF@microsoft.com...
> Hi
>
> I quoted the wrong time format (24hr clock) I have edited the original
> question and updated it to the correct format ( 12hr clock)
>
> Cheers
>
> "John Calder" wrote:
>
>> Hi
>>
>> I run Excel 2K
>>
>> I download data from a mainframe. This data has a date & time format in 
>> it
>> (dd/mm/yyyy hh:mm:ss)
>>
>> I work in a place that has a 3 shift cycle - day shift, afternoon shift,
>> night shift.
>>
>> Day shift starts at 7:20am and ends at 3:29pm
>> Afternoon shift starts at 3:30pm and ends at 11:19pm
>> Night shift starts at 11:20pm amd ends at 7:19am
>>
>> I need a formula that looks at the cell with the date/time in it and
>> displays the word DAY (for the time frame of day shift), AFTERNOON (for 
>> the
>> timeframe of afternoon shift) and NIGHT (for the timeframe of night 
>> shift)
>>
>> In my previous job I used a formula for a different shift pattern which 
>> was
>> 12 hour one which worked really well however the shift pattern that I 
>> need is
>> for is an 8 hour one, and I dont know how to edit the formula I used for 
>> the
>> 12 hour pattern. This wa based on a 24 hr clock time format.
>>
>> This is the formula I used for the 12 hour shift pattern:
>>
>> =IF(AND(TEXT(F12-INT(F12),"hh:mm:ss")>="07:00:00",TEXT(F12-INT(F12),"hh:mm:ss")<"19:00:00"),"Day","Night")
>>
>>
>> I hope I have managed to explain this ok.
>>
>> Thanks
>>
>> John 

0
joeu2004 (766)
8/18/2009 1:24:14 AM
I forgot to mention....

I wrote:
> =LOOKUP(TEXT(MOD(A1,1),"hh:mm:ss"),
>      {"00:00:01","07:20:00","15:30:00","23:20:00"},
>      {"NIGHT","DAY","AFTERNOON","NIGHT"})

A1-INT(A1), as you wrote before, is another way to write MOD(A1,1).  For 
some numbers, the form A1-INT(A1) is more reliable.  But MOD(A1,1) seems to 
work for all date/times from 1/1/1900 12:00:01 AM through 12/31/2999 
12:59:59 PM.  Good enough? ;)


----- original message -----

"JoeU2004" <joeu2004@hotmail.com> wrote in message 
news:uikcZK6HKHA.1340@TK2MSFTNGP05.phx.gbl...
> "John Calder" <JohnCalder@discussions.microsoft.com> wrote:
>> I need a formula that looks at the cell with the date/time in it and
>> displays the word DAY (for the time frame of day shift), AFTERNOON (for 
>> the
>> timeframe of afternoon shift) and NIGHT (for the timeframe of night 
>> shift)
>
> Try:
>
> =LOOKUP(TEXT(MOD(A1,1),"hh:mm:ss"),
>      {"00:00:01","07:20:00","15:30:00","23:20:00"},
>      {"NIGHT","DAY","AFTERNOON","NIGHT"})
>
>
>> I quoted the wrong time format (24hr clock) I have edited the original
>> question and updated it to the correct format ( 12hr clock)
>
> The format of the cell (A1) does not matter.  The internal form (date/time 
> serial number) remains the same.  The TEXT() expression above converts the 
> serial number to 24-hour time, which is needed for the proper lookup 
> comparison.
>
>
> ----- original message -----
>
> "John Calder" <JohnCalder@discussions.microsoft.com> wrote in message 
> news:BE470D46-E409-41B8-BFCA-15C6CC6BC8EF@microsoft.com...
>> Hi
>>
>> I quoted the wrong time format (24hr clock) I have edited the original
>> question and updated it to the correct format ( 12hr clock)
>>
>> Cheers
>>
>> "John Calder" wrote:
>>
>>> Hi
>>>
>>> I run Excel 2K
>>>
>>> I download data from a mainframe. This data has a date & time format in 
>>> it
>>> (dd/mm/yyyy hh:mm:ss)
>>>
>>> I work in a place that has a 3 shift cycle - day shift, afternoon shift,
>>> night shift.
>>>
>>> Day shift starts at 7:20am and ends at 3:29pm
>>> Afternoon shift starts at 3:30pm and ends at 11:19pm
>>> Night shift starts at 11:20pm amd ends at 7:19am
>>>
>>> I need a formula that looks at the cell with the date/time in it and
>>> displays the word DAY (for the time frame of day shift), AFTERNOON (for 
>>> the
>>> timeframe of afternoon shift) and NIGHT (for the timeframe of night 
>>> shift)
>>>
>>> In my previous job I used a formula for a different shift pattern which 
>>> was
>>> 12 hour one which worked really well however the shift pattern that I 
>>> need is
>>> for is an 8 hour one, and I dont know how to edit the formula I used for 
>>> the
>>> 12 hour pattern. This wa based on a 24 hr clock time format.
>>>
>>> This is the formula I used for the 12 hour shift pattern:
>>>
>>> =IF(AND(TEXT(F12-INT(F12),"hh:mm:ss")>="07:00:00",TEXT(F12-INT(F12),"hh:mm:ss")<"19:00:00"),"Day","Night")
>>>
>>>
>>> I hope I have managed to explain this ok.
>>>
>>> Thanks
>>>
>>> John
> 

0
joeu2004 (766)
8/18/2009 1:30:00 AM
Critical typo....

I wrote:
> =LOOKUP(TEXT(MOD(A1,1),"hh:mm:ss"),
>      {"00:00:01","07:20:00","15:30:00","23:20:00"},
>      {"NIGHT","DAY","AFTERNOON","NIGHT"})

=LOOKUP(TEXT(MOD(A1,1),"hh:mm:ss"),
      {"00:00:00","07:20:00","15:30:00","23:20:00"},
      {"NIGHT","DAY","AFTERNOON","NIGHT"})

"00:00:01" should be "00:00:00".


----- original message -----

"JoeU2004" <joeu2004@hotmail.com> wrote in message 
news:uikcZK6HKHA.1340@TK2MSFTNGP05.phx.gbl...
> "John Calder" <JohnCalder@discussions.microsoft.com> wrote:
>> I need a formula that looks at the cell with the date/time in it and
>> displays the word DAY (for the time frame of day shift), AFTERNOON (for 
>> the
>> timeframe of afternoon shift) and NIGHT (for the timeframe of night 
>> shift)
>
> Try:
>
> =LOOKUP(TEXT(MOD(A1,1),"hh:mm:ss"),
>      {"00:00:01","07:20:00","15:30:00","23:20:00"},
>      {"NIGHT","DAY","AFTERNOON","NIGHT"})
>
>
>> I quoted the wrong time format (24hr clock) I have edited the original
>> question and updated it to the correct format ( 12hr clock)
>
> The format of the cell (A1) does not matter.  The internal form (date/time 
> serial number) remains the same.  The TEXT() expression above converts the 
> serial number to 24-hour time, which is needed for the proper lookup 
> comparison.
>
>
> ----- original message -----
>
> "John Calder" <JohnCalder@discussions.microsoft.com> wrote in message 
> news:BE470D46-E409-41B8-BFCA-15C6CC6BC8EF@microsoft.com...
>> Hi
>>
>> I quoted the wrong time format (24hr clock) I have edited the original
>> question and updated it to the correct format ( 12hr clock)
>>
>> Cheers
>>
>> "John Calder" wrote:
>>
>>> Hi
>>>
>>> I run Excel 2K
>>>
>>> I download data from a mainframe. This data has a date & time format in 
>>> it
>>> (dd/mm/yyyy hh:mm:ss)
>>>
>>> I work in a place that has a 3 shift cycle - day shift, afternoon shift,
>>> night shift.
>>>
>>> Day shift starts at 7:20am and ends at 3:29pm
>>> Afternoon shift starts at 3:30pm and ends at 11:19pm
>>> Night shift starts at 11:20pm amd ends at 7:19am
>>>
>>> I need a formula that looks at the cell with the date/time in it and
>>> displays the word DAY (for the time frame of day shift), AFTERNOON (for 
>>> the
>>> timeframe of afternoon shift) and NIGHT (for the timeframe of night 
>>> shift)
>>>
>>> In my previous job I used a formula for a different shift pattern which 
>>> was
>>> 12 hour one which worked really well however the shift pattern that I 
>>> need is
>>> for is an 8 hour one, and I dont know how to edit the formula I used for 
>>> the
>>> 12 hour pattern. This wa based on a 24 hr clock time format.
>>>
>>> This is the formula I used for the 12 hour shift pattern:
>>>
>>> =IF(AND(TEXT(F12-INT(F12),"hh:mm:ss")>="07:00:00",TEXT(F12-INT(F12),"hh:mm:ss")<"19:00:00"),"Day","Night")
>>>
>>>
>>> I hope I have managed to explain this ok.
>>>
>>> Thanks
>>>
>>> John
> 

0
joeu2004 (766)
8/18/2009 1:33:32 AM
Joe

Thanks a lot, looks great, I will let you know if I encounter any problems 
with it....well done !!!!

"JoeU2004" wrote:

> Critical typo....
> 
> I wrote:
> > =LOOKUP(TEXT(MOD(A1,1),"hh:mm:ss"),
> >      {"00:00:01","07:20:00","15:30:00","23:20:00"},
> >      {"NIGHT","DAY","AFTERNOON","NIGHT"})
> 
> =LOOKUP(TEXT(MOD(A1,1),"hh:mm:ss"),
>       {"00:00:00","07:20:00","15:30:00","23:20:00"},
>       {"NIGHT","DAY","AFTERNOON","NIGHT"})
> 
> "00:00:01" should be "00:00:00".
> 
> 
> ----- original message -----
> 
> "JoeU2004" <joeu2004@hotmail.com> wrote in message 
> news:uikcZK6HKHA.1340@TK2MSFTNGP05.phx.gbl...
> > "John Calder" <JohnCalder@discussions.microsoft.com> wrote:
> >> I need a formula that looks at the cell with the date/time in it and
> >> displays the word DAY (for the time frame of day shift), AFTERNOON (for 
> >> the
> >> timeframe of afternoon shift) and NIGHT (for the timeframe of night 
> >> shift)
> >
> > Try:
> >
> > =LOOKUP(TEXT(MOD(A1,1),"hh:mm:ss"),
> >      {"00:00:01","07:20:00","15:30:00","23:20:00"},
> >      {"NIGHT","DAY","AFTERNOON","NIGHT"})
> >
> >
> >> I quoted the wrong time format (24hr clock) I have edited the original
> >> question and updated it to the correct format ( 12hr clock)
> >
> > The format of the cell (A1) does not matter.  The internal form (date/time 
> > serial number) remains the same.  The TEXT() expression above converts the 
> > serial number to 24-hour time, which is needed for the proper lookup 
> > comparison.
> >
> >
> > ----- original message -----
> >
> > "John Calder" <JohnCalder@discussions.microsoft.com> wrote in message 
> > news:BE470D46-E409-41B8-BFCA-15C6CC6BC8EF@microsoft.com...
> >> Hi
> >>
> >> I quoted the wrong time format (24hr clock) I have edited the original
> >> question and updated it to the correct format ( 12hr clock)
> >>
> >> Cheers
> >>
> >> "John Calder" wrote:
> >>
> >>> Hi
> >>>
> >>> I run Excel 2K
> >>>
> >>> I download data from a mainframe. This data has a date & time format in 
> >>> it
> >>> (dd/mm/yyyy hh:mm:ss)
> >>>
> >>> I work in a place that has a 3 shift cycle - day shift, afternoon shift,
> >>> night shift.
> >>>
> >>> Day shift starts at 7:20am and ends at 3:29pm
> >>> Afternoon shift starts at 3:30pm and ends at 11:19pm
> >>> Night shift starts at 11:20pm amd ends at 7:19am
> >>>
> >>> I need a formula that looks at the cell with the date/time in it and
> >>> displays the word DAY (for the time frame of day shift), AFTERNOON (for 
> >>> the
> >>> timeframe of afternoon shift) and NIGHT (for the timeframe of night 
> >>> shift)
> >>>
> >>> In my previous job I used a formula for a different shift pattern which 
> >>> was
> >>> 12 hour one which worked really well however the shift pattern that I 
> >>> need is
> >>> for is an 8 hour one, and I dont know how to edit the formula I used for 
> >>> the
> >>> 12 hour pattern. This wa based on a 24 hr clock time format.
> >>>
> >>> This is the formula I used for the 12 hour shift pattern:
> >>>
> >>> =IF(AND(TEXT(F12-INT(F12),"hh:mm:ss")>="07:00:00",TEXT(F12-INT(F12),"hh:mm:ss")<"19:00:00"),"Day","Night")
> >>>
> >>>
> >>> I hope I have managed to explain this ok.
> >>>
> >>> Thanks
> >>>
> >>> John
> > 
> 
> 
0
JohnCalder (178)
8/18/2009 1:53:01 AM
"T. Valko" <biffinpitt@comcast.net> wrote:
> =LOOKUP(MOD(A1,1),J$1:K$4)

Test when A1 contains 1/1/2009 7:20 AM and 1/1/2009 11:20 PM.

I think you'll find that they return the wrong shift, namely:  NIGHT and 
AFTERNOON.

The reason is floating point arithmetic aberrations.  MOD(A1,1) yields 
0.305555555554747,1158206462860107421875 and
0.972222222218988,46328258514404296875 in those case, whereas 7:20 and 23:20 
(without dates) -- the equivalent of TIME(7,20,0) and TIME(23,20,0) -- are
0.305555555555555,524716027093745651654899120330810546875 and
0.972222222222222,20988641083749826066195964813232421875.

The success or failure LOOKUP(MOD(A1,1),...) will depend on the date as well 
as the time.  For example, it works for 1/1/2009 3:30 PM, but only by 
coincidence.  In that case, MOD(A1,1) yields
0.645833333335758,6525380611419677734375, and 15:30 is
0.645833333333333,37034076748750521801412105560302734375.

As usual, the solution is to "round" the time values.  In this 
case, --TEXT(MOD(A1,1),"hh:mm:ss") would exactly match the internal 
representation of any time in the form "hh:mm:ss".

But for the OP's problem, we do not need --TEXT().  We can look up the 
TEXT() result.


----- original message -----

"T. Valko" <biffinpitt@comcast.net> wrote in message 
news:eP3ccA6HKHA.1492@TK2MSFTNGP03.phx.gbl...
> Assuming your dates/times are true Excel dates/times.
>
> One way...
>
> Create a lookup table like this:
>
> ..........J..........K
> 1....0:00.....Night
> 2....7:20.....Day
> 3..15:30.....Afternoon
> 4..23:20.....Night
>
> Then...
>
> A1 = some dd/mm/yyyy hh:mm:ss
>
> =LOOKUP(MOD(A1,1),J$1:K$4)
>
> -- 
> Biff
> Microsoft Excel MVP
>
>
> "John Calder" <JohnCalder@discussions.microsoft.com> wrote in message 
> news:745C71FA-3509-42AD-B0C4-E5B8E1540E9F@microsoft.com...
>> Hi
>>
>> I run Excel 2K
>>
>> I download data from a mainframe. This data has a date & time format in 
>> it
>> (dd/mm/yyyy hh:mm:ss)
>>
>> I work in a place that has a 3 shift cycle - day shift, afternoon shift,
>> night shift.
>>
>> Day shift starts at 7:20am and ends at 15:29pm
>> Afternoon shift starts at 15:30pm and ends at 23:19pm
>> Night shift starts at 23:20pm amd ends at 7:19am
>>
>> I need a formula that looks at the cell with the date/time in it and
>> displays the word DAY (for the time frame of day shift), AFTERNOON (for 
>> the
>> timeframe of afternoon shift) and NIGHT (for the timeframe of night 
>> shift)
>>
>> In my previous job I used a formula for a different shift pattern which 
>> was
>> 12 hour one which worked really well however the shift pattern that I 
>> need is
>> for is an 8 hour one, and I dont know how to edit the formula I used for 
>> the
>> 12 hour pattern.
>>
>> This is the formula I used for the 12 hour shift pattern:
>>
>> =IF(AND(TEXT(F12-INT(F12),"hh:mm:ss")>="07:00:00",TEXT(F12-INT(F12),"hh:mm:ss")<"19:00:00"),"Day","Night")
>>
>>
>> I hope I have managed to explain this ok.
>>
>> Thanks
>>
>> John
>
> 

0
joeu2004 (766)
8/18/2009 1:54:51 AM
>I think you'll find that they return the wrong shift,
>namely:  NIGHT and AFTERNOON.

Yep, my fault for not testing with a date. I figured the date was irrelevant 
so I tested with *times only*.

>The reason is floating point arithmetic aberrations.

When it comes to rounding time values to account for this behavior I'm not 
real sure about how many decimal places to set the rounding. Got a good 
"rule of thumb" recommendation for that?

-- 
Biff
Microsoft Excel MVP


"JoeU2004" <joeu2004@hotmail.com> wrote in message 
news:O74Vgb6HKHA.5956@TK2MSFTNGP03.phx.gbl...
> "T. Valko" <biffinpitt@comcast.net> wrote:
>> =LOOKUP(MOD(A1,1),J$1:K$4)
>
> Test when A1 contains 1/1/2009 7:20 AM and 1/1/2009 11:20 PM.
>
> I think you'll find that they return the wrong shift, namely:  NIGHT and 
> AFTERNOON.
>
> The reason is floating point arithmetic aberrations.  MOD(A1,1) yields 
> 0.305555555554747,1158206462860107421875 and
> 0.972222222218988,46328258514404296875 in those case, whereas 7:20 and 
> 23:20 (without dates) -- the equivalent of TIME(7,20,0) and 
> TIME(23,20,0) -- are
> 0.305555555555555,524716027093745651654899120330810546875 and
> 0.972222222222222,20988641083749826066195964813232421875.
>
> The success or failure LOOKUP(MOD(A1,1),...) will depend on the date as 
> well as the time.  For example, it works for 1/1/2009 3:30 PM, but only by 
> coincidence.  In that case, MOD(A1,1) yields
> 0.645833333335758,6525380611419677734375, and 15:30 is
> 0.645833333333333,37034076748750521801412105560302734375.
>
> As usual, the solution is to "round" the time values.  In this 
> case, --TEXT(MOD(A1,1),"hh:mm:ss") would exactly match the internal 
> representation of any time in the form "hh:mm:ss".
>
> But for the OP's problem, we do not need --TEXT().  We can look up the 
> TEXT() result.
>
>
> ----- original message -----
>
> "T. Valko" <biffinpitt@comcast.net> wrote in message 
> news:eP3ccA6HKHA.1492@TK2MSFTNGP03.phx.gbl...
>> Assuming your dates/times are true Excel dates/times.
>>
>> One way...
>>
>> Create a lookup table like this:
>>
>> ..........J..........K
>> 1....0:00.....Night
>> 2....7:20.....Day
>> 3..15:30.....Afternoon
>> 4..23:20.....Night
>>
>> Then...
>>
>> A1 = some dd/mm/yyyy hh:mm:ss
>>
>> =LOOKUP(MOD(A1,1),J$1:K$4)
>>
>> -- 
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "John Calder" <JohnCalder@discussions.microsoft.com> wrote in message 
>> news:745C71FA-3509-42AD-B0C4-E5B8E1540E9F@microsoft.com...
>>> Hi
>>>
>>> I run Excel 2K
>>>
>>> I download data from a mainframe. This data has a date & time format in 
>>> it
>>> (dd/mm/yyyy hh:mm:ss)
>>>
>>> I work in a place that has a 3 shift cycle - day shift, afternoon shift,
>>> night shift.
>>>
>>> Day shift starts at 7:20am and ends at 15:29pm
>>> Afternoon shift starts at 15:30pm and ends at 23:19pm
>>> Night shift starts at 23:20pm amd ends at 7:19am
>>>
>>> I need a formula that looks at the cell with the date/time in it and
>>> displays the word DAY (for the time frame of day shift), AFTERNOON (for 
>>> the
>>> timeframe of afternoon shift) and NIGHT (for the timeframe of night 
>>> shift)
>>>
>>> In my previous job I used a formula for a different shift pattern which 
>>> was
>>> 12 hour one which worked really well however the shift pattern that I 
>>> need is
>>> for is an 8 hour one, and I dont know how to edit the formula I used for 
>>> the
>>> 12 hour pattern.
>>>
>>> This is the formula I used for the 12 hour shift pattern:
>>>
>>> =IF(AND(TEXT(F12-INT(F12),"hh:mm:ss")>="07:00:00",TEXT(F12-INT(F12),"hh:mm:ss")<"19:00:00"),"Day","Night")
>>>
>>>
>>> I hope I have managed to explain this ok.
>>>
>>> Thanks
>>>
>>> John
>>
>>
> 


0
biffinpitt (3172)
8/18/2009 2:37:52 AM
"T. Valko" <biffinpitt@comcast.net> wrote:
> When it comes to rounding time values to account
> for this behavior I'm not real sure about how many
> decimal places to set the rounding. Got a good
> "rule of thumb" recommendation for that?

Exactly!  That's why I put "round" in quotes and suggested using --TEXT() 
with an appropriate time format.

There are arithmetic methods.  For example, ROUND(...*1440,0)/1440 would 
seem to round to a minute; and ROUND(...*86400,0)/86400 would seem to round 
to a second.

And those do seem to work well in the few cases that I've tried, even with a 
date component.  When I say "work", I mean that they result in the same 
internal binary value as if the date/time were entered manually.

But I'm relunctant to use that approach because it uses floating point 
division.  It probably does "work", as I defined the term above.  But I 
would need to give it more thought.


----- original message -----

"T. Valko" <biffinpitt@comcast.net> wrote in message
news:uKBvkz6HKHA.4024@TK2MSFTNGP02.phx.gbl...
> >I think you'll find that they return the wrong shift,
>>namely:  NIGHT and AFTERNOON.
>
> Yep, my fault for not testing with a date. I figured the date was
> irrelevant so I tested with *times only*.
>
>>The reason is floating point arithmetic aberrations.
>
> When it comes to rounding time values to account for this behavior I'm not
> real sure about how many decimal places to set the rounding. Got a good
> "rule of thumb" recommendation for that?
>
> -- 
> Biff
> Microsoft Excel MVP
>
>
> "JoeU2004" <joeu2004@hotmail.com> wrote in message
> news:O74Vgb6HKHA.5956@TK2MSFTNGP03.phx.gbl...
>> "T. Valko" <biffinpitt@comcast.net> wrote:
>>> =LOOKUP(MOD(A1,1),J$1:K$4)
>>
>> Test when A1 contains 1/1/2009 7:20 AM and 1/1/2009 11:20 PM.
>>
>> I think you'll find that they return the wrong shift, namely:  NIGHT and
>> AFTERNOON.
>>
>> The reason is floating point arithmetic aberrations.  MOD(A1,1) yields
>> 0.305555555554747,1158206462860107421875 and
>> 0.972222222218988,46328258514404296875 in those case, whereas 7:20 and
>> 23:20 (without dates) -- the equivalent of TIME(7,20,0) and
>> TIME(23,20,0) -- are
>> 0.305555555555555,524716027093745651654899120330810546875 and
>> 0.972222222222222,20988641083749826066195964813232421875.
>>
>> The success or failure LOOKUP(MOD(A1,1),...) will depend on the date as
>> well as the time.  For example, it works for 1/1/2009 3:30 PM, but only
>> by coincidence.  In that case, MOD(A1,1) yields
>> 0.645833333335758,6525380611419677734375, and 15:30 is
>> 0.645833333333333,37034076748750521801412105560302734375.
>>
>> As usual, the solution is to "round" the time values.  In this
>> case, --TEXT(MOD(A1,1),"hh:mm:ss") would exactly match the internal
>> representation of any time in the form "hh:mm:ss".
>>
>> But for the OP's problem, we do not need --TEXT().  We can look up the
>> TEXT() result.
>>
>>
>> ----- original message -----
>>
>> "T. Valko" <biffinpitt@comcast.net> wrote in message
>> news:eP3ccA6HKHA.1492@TK2MSFTNGP03.phx.gbl...
>>> Assuming your dates/times are true Excel dates/times.
>>>
>>> One way...
>>>
>>> Create a lookup table like this:
>>>
>>> ..........J..........K
>>> 1....0:00.....Night
>>> 2....7:20.....Day
>>> 3..15:30.....Afternoon
>>> 4..23:20.....Night
>>>
>>> Then...
>>>
>>> A1 = some dd/mm/yyyy hh:mm:ss
>>>
>>> =LOOKUP(MOD(A1,1),J$1:K$4)
>>>
>>> -- 
>>> Biff
>>> Microsoft Excel MVP
>>>
>>>
>>> "John Calder" <JohnCalder@discussions.microsoft.com> wrote in message
>>> news:745C71FA-3509-42AD-B0C4-E5B8E1540E9F@microsoft.com...
>>>> Hi
>>>>
>>>> I run Excel 2K
>>>>
>>>> I download data from a mainframe. This data has a date & time format in
>>>> it
>>>> (dd/mm/yyyy hh:mm:ss)
>>>>
>>>> I work in a place that has a 3 shift cycle - day shift, afternoon
>>>> shift,
>>>> night shift.
>>>>
>>>> Day shift starts at 7:20am and ends at 15:29pm
>>>> Afternoon shift starts at 15:30pm and ends at 23:19pm
>>>> Night shift starts at 23:20pm amd ends at 7:19am
>>>>
>>>> I need a formula that looks at the cell with the date/time in it and
>>>> displays the word DAY (for the time frame of day shift), AFTERNOON (for
>>>> the
>>>> timeframe of afternoon shift) and NIGHT (for the timeframe of night
>>>> shift)
>>>>
>>>> In my previous job I used a formula for a different shift pattern which
>>>> was
>>>> 12 hour one which worked really well however the shift pattern that I
>>>> need is
>>>> for is an 8 hour one, and I dont know how to edit the formula I used
>>>> for the
>>>> 12 hour pattern.
>>>>
>>>> This is the formula I used for the 12 hour shift pattern:
>>>>
>>>> =IF(AND(TEXT(F12-INT(F12),"hh:mm:ss")>="07:00:00",TEXT(F12-INT(F12),"hh:mm:ss")<"19:00:00"),"Day","Night")
>>>>
>>>>
>>>> I hope I have managed to explain this ok.
>>>>
>>>> Thanks
>>>>
>>>> John
>>>
>>>
>>
>
>

0
joeu2004 (766)
8/19/2009 4:13:18 PM
Ok, so at least I'm not alone in thinking that the rounding of time values 
is not as simple as one would guess.

-- 
Biff
Microsoft Excel MVP


"JoeU2004" <joeu2004@hotmail.com> wrote in message 
news:Ouah3fOIKHA.4376@TK2MSFTNGP03.phx.gbl...
> "T. Valko" <biffinpitt@comcast.net> wrote:
>> When it comes to rounding time values to account
>> for this behavior I'm not real sure about how many
>> decimal places to set the rounding. Got a good
>> "rule of thumb" recommendation for that?
>
> Exactly!  That's why I put "round" in quotes and suggested using --TEXT() 
> with an appropriate time format.
>
> There are arithmetic methods.  For example, ROUND(...*1440,0)/1440 would 
> seem to round to a minute; and ROUND(...*86400,0)/86400 would seem to 
> round to a second.
>
> And those do seem to work well in the few cases that I've tried, even with 
> a date component.  When I say "work", I mean that they result in the same 
> internal binary value as if the date/time were entered manually.
>
> But I'm relunctant to use that approach because it uses floating point 
> division.  It probably does "work", as I defined the term above.  But I 
> would need to give it more thought.
>
>
> ----- original message -----
>
> "T. Valko" <biffinpitt@comcast.net> wrote in message
> news:uKBvkz6HKHA.4024@TK2MSFTNGP02.phx.gbl...
>> >I think you'll find that they return the wrong shift,
>>>namely:  NIGHT and AFTERNOON.
>>
>> Yep, my fault for not testing with a date. I figured the date was
>> irrelevant so I tested with *times only*.
>>
>>>The reason is floating point arithmetic aberrations.
>>
>> When it comes to rounding time values to account for this behavior I'm 
>> not
>> real sure about how many decimal places to set the rounding. Got a good
>> "rule of thumb" recommendation for that?
>>
>> -- 
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "JoeU2004" <joeu2004@hotmail.com> wrote in message
>> news:O74Vgb6HKHA.5956@TK2MSFTNGP03.phx.gbl...
>>> "T. Valko" <biffinpitt@comcast.net> wrote:
>>>> =LOOKUP(MOD(A1,1),J$1:K$4)
>>>
>>> Test when A1 contains 1/1/2009 7:20 AM and 1/1/2009 11:20 PM.
>>>
>>> I think you'll find that they return the wrong shift, namely:  NIGHT and
>>> AFTERNOON.
>>>
>>> The reason is floating point arithmetic aberrations.  MOD(A1,1) yields
>>> 0.305555555554747,1158206462860107421875 and
>>> 0.972222222218988,46328258514404296875 in those case, whereas 7:20 and
>>> 23:20 (without dates) -- the equivalent of TIME(7,20,0) and
>>> TIME(23,20,0) -- are
>>> 0.305555555555555,524716027093745651654899120330810546875 and
>>> 0.972222222222222,20988641083749826066195964813232421875.
>>>
>>> The success or failure LOOKUP(MOD(A1,1),...) will depend on the date as
>>> well as the time.  For example, it works for 1/1/2009 3:30 PM, but only
>>> by coincidence.  In that case, MOD(A1,1) yields
>>> 0.645833333335758,6525380611419677734375, and 15:30 is
>>> 0.645833333333333,37034076748750521801412105560302734375.
>>>
>>> As usual, the solution is to "round" the time values.  In this
>>> case, --TEXT(MOD(A1,1),"hh:mm:ss") would exactly match the internal
>>> representation of any time in the form "hh:mm:ss".
>>>
>>> But for the OP's problem, we do not need --TEXT().  We can look up the
>>> TEXT() result.
>>>
>>>
>>> ----- original message -----
>>>
>>> "T. Valko" <biffinpitt@comcast.net> wrote in message
>>> news:eP3ccA6HKHA.1492@TK2MSFTNGP03.phx.gbl...
>>>> Assuming your dates/times are true Excel dates/times.
>>>>
>>>> One way...
>>>>
>>>> Create a lookup table like this:
>>>>
>>>> ..........J..........K
>>>> 1....0:00.....Night
>>>> 2....7:20.....Day
>>>> 3..15:30.....Afternoon
>>>> 4..23:20.....Night
>>>>
>>>> Then...
>>>>
>>>> A1 = some dd/mm/yyyy hh:mm:ss
>>>>
>>>> =LOOKUP(MOD(A1,1),J$1:K$4)
>>>>
>>>> -- 
>>>> Biff
>>>> Microsoft Excel MVP
>>>>
>>>>
>>>> "John Calder" <JohnCalder@discussions.microsoft.com> wrote in message
>>>> news:745C71FA-3509-42AD-B0C4-E5B8E1540E9F@microsoft.com...
>>>>> Hi
>>>>>
>>>>> I run Excel 2K
>>>>>
>>>>> I download data from a mainframe. This data has a date & time format 
>>>>> in
>>>>> it
>>>>> (dd/mm/yyyy hh:mm:ss)
>>>>>
>>>>> I work in a place that has a 3 shift cycle - day shift, afternoon
>>>>> shift,
>>>>> night shift.
>>>>>
>>>>> Day shift starts at 7:20am and ends at 15:29pm
>>>>> Afternoon shift starts at 15:30pm and ends at 23:19pm
>>>>> Night shift starts at 23:20pm amd ends at 7:19am
>>>>>
>>>>> I need a formula that looks at the cell with the date/time in it and
>>>>> displays the word DAY (for the time frame of day shift), AFTERNOON 
>>>>> (for
>>>>> the
>>>>> timeframe of afternoon shift) and NIGHT (for the timeframe of night
>>>>> shift)
>>>>>
>>>>> In my previous job I used a formula for a different shift pattern 
>>>>> which
>>>>> was
>>>>> 12 hour one which worked really well however the shift pattern that I
>>>>> need is
>>>>> for is an 8 hour one, and I dont know how to edit the formula I used
>>>>> for the
>>>>> 12 hour pattern.
>>>>>
>>>>> This is the formula I used for the 12 hour shift pattern:
>>>>>
>>>>> =IF(AND(TEXT(F12-INT(F12),"hh:mm:ss")>="07:00:00",TEXT(F12-INT(F12),"hh:mm:ss")<"19:00:00"),"Day","Night")
>>>>>
>>>>>
>>>>> I hope I have managed to explain this ok.
>>>>>
>>>>> Thanks
>>>>>
>>>>> John
>>>>
>>>>
>>>
>>
>>
> 


0
biffinpitt (3172)
8/19/2009 5:15:50 PM
Errata....

TEXT(...,"hh:mm") truncates to the minute; it does not round.

The equivalent arithmetic method is TRUNC(...*1440)/1440.  (But see floating 
point concerns expressed in my original message below.)

PS:  We can use TEXT(...+TIME(0,0,30),"hh:mm") to round, and 
TEXT(...+TIME(0,0,59),"hh:mm") to round up.


----- original message -----

"JoeU2004" <joeu2004@hotmail.com> wrote in message 
news:Ouah3fOIKHA.4376@TK2MSFTNGP03.phx.gbl...
> "T. Valko" <biffinpitt@comcast.net> wrote:
>> When it comes to rounding time values to account
>> for this behavior I'm not real sure about how many
>> decimal places to set the rounding. Got a good
>> "rule of thumb" recommendation for that?
>
> Exactly!  That's why I put "round" in quotes and suggested using --TEXT() 
> with an appropriate time format.
>
> There are arithmetic methods.  For example, ROUND(...*1440,0)/1440 would 
> seem to round to a minute; and ROUND(...*86400,0)/86400 would seem to 
> round to a second.
>
> And those do seem to work well in the few cases that I've tried, even with 
> a date component.  When I say "work", I mean that they result in the same 
> internal binary value as if the date/time were entered manually.
>
> But I'm relunctant to use that approach because it uses floating point 
> division.  It probably does "work", as I defined the term above.  But I 
> would need to give it more thought.
>
>
> ----- original message -----
>
> "T. Valko" <biffinpitt@comcast.net> wrote in message
> news:uKBvkz6HKHA.4024@TK2MSFTNGP02.phx.gbl...
>> >I think you'll find that they return the wrong shift,
>>>namely:  NIGHT and AFTERNOON.
>>
>> Yep, my fault for not testing with a date. I figured the date was
>> irrelevant so I tested with *times only*.
>>
>>>The reason is floating point arithmetic aberrations.
>>
>> When it comes to rounding time values to account for this behavior I'm 
>> not
>> real sure about how many decimal places to set the rounding. Got a good
>> "rule of thumb" recommendation for that?
>>
>> -- 
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "JoeU2004" <joeu2004@hotmail.com> wrote in message
>> news:O74Vgb6HKHA.5956@TK2MSFTNGP03.phx.gbl...
>>> "T. Valko" <biffinpitt@comcast.net> wrote:
>>>> =LOOKUP(MOD(A1,1),J$1:K$4)
>>>
>>> Test when A1 contains 1/1/2009 7:20 AM and 1/1/2009 11:20 PM.
>>>
>>> I think you'll find that they return the wrong shift, namely:  NIGHT and
>>> AFTERNOON.
>>>
>>> The reason is floating point arithmetic aberrations.  MOD(A1,1) yields
>>> 0.305555555554747,1158206462860107421875 and
>>> 0.972222222218988,46328258514404296875 in those case, whereas 7:20 and
>>> 23:20 (without dates) -- the equivalent of TIME(7,20,0) and
>>> TIME(23,20,0) -- are
>>> 0.305555555555555,524716027093745651654899120330810546875 and
>>> 0.972222222222222,20988641083749826066195964813232421875.
>>>
>>> The success or failure LOOKUP(MOD(A1,1),...) will depend on the date as
>>> well as the time.  For example, it works for 1/1/2009 3:30 PM, but only
>>> by coincidence.  In that case, MOD(A1,1) yields
>>> 0.645833333335758,6525380611419677734375, and 15:30 is
>>> 0.645833333333333,37034076748750521801412105560302734375.
>>>
>>> As usual, the solution is to "round" the time values.  In this
>>> case, --TEXT(MOD(A1,1),"hh:mm:ss") would exactly match the internal
>>> representation of any time in the form "hh:mm:ss".
>>>
>>> But for the OP's problem, we do not need --TEXT().  We can look up the
>>> TEXT() result.
>>>
>>>
>>> ----- original message -----
>>>
>>> "T. Valko" <biffinpitt@comcast.net> wrote in message
>>> news:eP3ccA6HKHA.1492@TK2MSFTNGP03.phx.gbl...
>>>> Assuming your dates/times are true Excel dates/times.
>>>>
>>>> One way...
>>>>
>>>> Create a lookup table like this:
>>>>
>>>> ..........J..........K
>>>> 1....0:00.....Night
>>>> 2....7:20.....Day
>>>> 3..15:30.....Afternoon
>>>> 4..23:20.....Night
>>>>
>>>> Then...
>>>>
>>>> A1 = some dd/mm/yyyy hh:mm:ss
>>>>
>>>> =LOOKUP(MOD(A1,1),J$1:K$4)
>>>>
>>>> -- 
>>>> Biff
>>>> Microsoft Excel MVP
>>>>
>>>>
>>>> "John Calder" <JohnCalder@discussions.microsoft.com> wrote in message
>>>> news:745C71FA-3509-42AD-B0C4-E5B8E1540E9F@microsoft.com...
>>>>> Hi
>>>>>
>>>>> I run Excel 2K
>>>>>
>>>>> I download data from a mainframe. This data has a date & time format 
>>>>> in
>>>>> it
>>>>> (dd/mm/yyyy hh:mm:ss)
>>>>>
>>>>> I work in a place that has a 3 shift cycle - day shift, afternoon
>>>>> shift,
>>>>> night shift.
>>>>>
>>>>> Day shift starts at 7:20am and ends at 15:29pm
>>>>> Afternoon shift starts at 15:30pm and ends at 23:19pm
>>>>> Night shift starts at 23:20pm amd ends at 7:19am
>>>>>
>>>>> I need a formula that looks at the cell with the date/time in it and
>>>>> displays the word DAY (for the time frame of day shift), AFTERNOON 
>>>>> (for
>>>>> the
>>>>> timeframe of afternoon shift) and NIGHT (for the timeframe of night
>>>>> shift)
>>>>>
>>>>> In my previous job I used a formula for a different shift pattern 
>>>>> which
>>>>> was
>>>>> 12 hour one which worked really well however the shift pattern that I
>>>>> need is
>>>>> for is an 8 hour one, and I dont know how to edit the formula I used
>>>>> for the
>>>>> 12 hour pattern.
>>>>>
>>>>> This is the formula I used for the 12 hour shift pattern:
>>>>>
>>>>> =IF(AND(TEXT(F12-INT(F12),"hh:mm:ss")>="07:00:00",TEXT(F12-INT(F12),"hh:mm:ss")<"19:00:00"),"Day","Night")
>>>>>
>>>>>
>>>>> I hope I have managed to explain this ok.
>>>>>
>>>>> Thanks
>>>>>
>>>>> John
>>>>
>>>>
>>>
>>
>>
> 

0
joeu2004 (766)
8/20/2009 6:42:14 AM
Reply:

Similar Artilces:

Copy Formula with Reference Change
I have a workbook that will have 100+ sheets in it. I want to use a beginning number in a cell on the first sheet and then on the following sheets in the same cell have a formula that adds 1 to the number in the same cell on the previous sheet. For example, the first sheet has 497 in cell B2. In each successive sheet, I would have a formula in cell B2 that would be '=sheet1!B2+1' to give me 498, then 499, then 500, etc. I want to copy the formula to each successive sheet and have it automatically adjust the sheet it is referencing to be the previous sheet. However, when ...

Time in Excel '97
Is there a way in Excel '97 to get it to insert the exact time? What I mean is that if I push Ctrl+Shift+:, the time inserts only hours, minutes and AM/PM, but no seconds. Actually, I just tried this in Excel XP and it does the same thing. Any ideas? Would really appreciate it. Matt You could put this code in your personal.xls workbook and assign it to a keyboard shortcut. Sub InsertTimeWithSecs() ActiveCell.Value = Format(Now(), "hh:mm:ss") End Sub -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "Matt J."...

configuration wizard every time in Office 2007
configuration wizard run every time I start an Office 2007 standard application You probably not install Office correctly. Reinstall office and INSTALL ALL COMPONENTS. Are you running both Word 2007 and Word 2003 on the same machine? If so, see the "Hacking the Registry" section of the article at http://www.gmayor.com/Toolbars_in_word_2007.htm. If that's not it, try logging in as Administrator and see if that makes the installation finish correctly. -- Stefan Blom Microsoft Word MVP "Mark Young" <Mark Young@discussions.microsoft.com> ...

search #2
Can someone tell me why when I do a search and just put in the name thats in the from catagory in the news groups it will not come up with anything. hope you can help steve Hi Steve, Let's start from the beginning because I don't know what you are actually doing. The following link will set you up to search the *Excel* newsgroups using Google Advanced Groups search http://groups.google.com/advanced_group_search?q=group:*Excel*&num=100 into the author field you can enter something like one of the following stevem@luminet.net Tom Ogilvy My webpage to help with such thin...

"The Operation Failed" #2
This is the message I get when I try to open Outlook 2000. I'm running Win2K. I recently had a serious crash and my computer is still not stable. However, I AM able to run Outlook Express. I've had viruses, which are cleaned out. (Bliss, Hacktool.Sechole). And whenever I start up any of the Office programs, they go through the "installation" process as if they were never started before. It's as if the computer has no memory that they've already been installed. Any assistance would be greatly appreciated. Thank you. ...

User not accessed the system since last 2 months
Is there any script available to determine accounts that have not logged in for the past x months. Please provide us the link and and guide line how to use that ? Thanks ...

Unhandled Script Exception #2
Hi, We get the following error when trying to post a G/L batch: Unhandled Script Exception: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure 'AVDYN.dbo.glpCreatePostingActivity' We are using Dynamics 8-SP2 Thanks for your help, Marc R. - Avanti Computer Systems recreate or restore the glpCreatePostingActivity stored Procedure will solve the issue. "Marc R." wrote: > Hi, > We get the following error when trying to post a G/L > batch: > > Unhandled Script Exception: > [Microsoft][ODBC SQL Server Driver][SQL Server]Could...

Condtional format with formula
I have this formula =IF(ISNA(VLOOKUP(A17,'Data-FSList'!GEAC2000IntlFSList,2,FALSE)) =TRUE, "DONOR NOT VALID",VLOOKUP(A17,'Data-FSList'!GEAC2000IntlFSList,2,FALSE)) on cell B17. When the user selects a value in A17 from the drop down, it goes to sheet 'Data-FSList' to retrieve a value. If the value is not there it comes back in field B17 'DONOR NOT VALID' However when A17 contains no value from the drop down and is blank I do not want 'DONOR NOT VALID' to show in cell B17. Is there a way to add to the formula above if A17 is blank then...

Delivery Status Notification (Delay) #2
Hi, I have Exchange 2003. There are some remote user (using POP3)got messages like below from postmaster when sending email: This is an automatically generated Delivery Status Notification. THIS IS A WARNING MESSAGE ONLY. YOU DO NOT NEED TO RESEND YOUR MESSAGE. Delivery to the following recipients has been delayed. xxxx@xxx.com Internal user (useing corporate/workgroup mode in Outlook) has no problem. No problem from OWA. The remote POP3 user is submitting the message over an authenticated SMTP connection. We don't allow unauthenticated email to be routed through our se...

Importing data #2
hi I would like to import data from the web and although this is quite simple in Excel 2003 the new data (that comes in upon refresh) replaces the old. I was wondering if there is a way to keep previous history of the data, make Excel fill in adjacent cells with the new data for example. Thanks for taking the time to read this George hi, if you are using Microsoft query, the answer would be no. The Micorsoft query becomes a named range in excel that is linked to the query. by desing it expands and contracts at refresh to accommidate new data. I think to accomplish what you want yo...

Controlling execution of a Sub Procedure -Part 2 :(
Question:Is there any way to only start a Worksheet_calculate procedure once the iterative sheet with which it is associated is FINISHED? How can you detect when it is finished? Details below In a previous post I was trying to run a lengthy calculation in a Sub Worksheet_calculate() procedure IF certain conditions on a spreadsheet were met. Of course I was developing this outside the ultimate workbook application. I resolved my stack overflow problem by writing out results to a sheet other than the one that had the Worksheet_calculate() procedure associated with it. THAT d...

Counting with 2 Conditions
I created a report and would like to get a count of records with two conditions. Here is what I entered: =Abs(Sum(IIf([terminate]=-1,1,0)) And [caseload]="SA") This information is entered in an unbound text. I would like to get a total count where the terminate field (yes/not) is checked and the caseload meets a certain criteria. Thank you! TomP wrote: >I created a report and would like to get a count of records with two >conditions. Here is what I entered: > >=Abs(Sum(IIf([terminate]=-1,1,0)) And [caseload]="SA") > >This information is ent...

Formula Addition incorrect
This must be a simple error, as all I am doing is calculating sales tax. I have column B as Gross rents, column c with the formula =sum(B11_-Sum(b11/1.065). And column D with the net rent. Then I ask it to total all columns down. But the Column total from the calculations, does not add correctly. It has a 3 cent difference. Add the figures manually and the total is correct. I.e. Gross Rent Sales tax Net Rent 900 =Sum(b11)-sum(b11/1.065) 845.07 This column does not add down correctly. Can anyone suggest why. There are only 1...

crm token #2
some times i track an email in crm sent it to a customer- i got the customer repaly to the mail but i dont see the traking icon in the replay message and it seemed to be un tracked but if i go to the customer history i see his replay inside the history further more if i press track in crm to his replay he'll give me directly the regarding field filled with this customer have some body an idia about this see this http://support.microsoft.com/kb/929385/en-us try to install rollup update 2 and it will work. -- Regards, Imran MS CRM Certified Professional http://microsoftcrm3.blo...

Data Labels on Clustered Column w/ 3D Visual effect #2
Clustered Columns can have Alignment as "Inside End" This doesn't seem possible with 3D Visual effect Is there a way to resolve this? Yes, use 2D charts. They provide a better representation of the values being plotted without the implied false third dimension. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ emwashburn wrote: > Clustered Columns can have Alignment as "Inside End" > > This doesn't seem possible with 3D Visual effect > > Is there a way to ...

"IF" Formula
xl2000—Totaling time worked at each of three stores for a two-week pay period. What I have is Columns C, D, E, F, G, H, & I, are week one. Columns M, N, O, P, Q, R, S, are week two. I have, through data validation, dropdowns to select Store 1, Store 2, or Store 3, in row 7 of each of the described columns. The employee will select the store worked for that day, by column (C> Monday, D>Tuesday, E> Wednesday, etc). Then enter the time worked in the same column. In row 18 are the total hours for each day worked. Now I need to summaries the time worked for each store thro...

dynamic charts using named formulas
I have dates in the first row I have values in the second row I use tecnique of named formuls of offset function (tushar Methta) so that the chart is updated when data for new dates are added. now after some time I want to delete the older data If I delete the columns of older dates the named formula get messed up and I get error message that three is some wrong reference. actulally in the offset formula the starting cell is relaced by "REF" Then I have to redo the whole exercise any solutions. If the first row is some sort of header that doesn't get deleted, use it as the ba...

XML Insert data C# ASP.net 2.0 Need to know how to insert by speci
Hello, I have the following XML Schema: <?xml version="1.0" encoding="utf-8"?> <Schedule> <Day Name="Monday"> </Day> <Day Name="Tuesday"> </Day> <Day Name="Wednesday"> </Day> <Day Name="Thursday"> </Day> <Day Name="Friday"> </Day> <Day Name="Saturday"> </Day> <Day Name="Sunday"> </Day> </Schedule> I want users to be able to input the following tiomeblocks for a scheduling s...

Rename name so formulas show name change
Excel 2003 Cell A1 is named MyCell. Cell A2 has formula "=MyCell". I rename cell A1 in the toolbar to NewCell. Cell A2 still has the formula "=MyCell". How do I rename cell A1 to NewCell and have it automatically change cell A2 to the formula "=NewCell"? Thank you for your help. Download Jan Karel Pieterse'e Name Manager here: http://www.jkp-ads.com/OfficeMarketPlaceNM-EN.asp -- Kind regards, Niek Otten Microsoft MVP - Excel "John" <no.email@no.email.com> wrote in message news:ORne6k3$GHA.4592@TK2MSFTNGP03.phx.gbl... | Excel 2003 |...

Convert C:\1\2.exe to c:\1\ shlwapi
Looking a function to do that. shlwapi.lib Maybe someone else here will understand this, but it looks like a typo to me. You want to convert: C:\1\2.exe to C:\1\shlwapi (what?) Is this shortname to long name? I guess what I'm asking is what does 2.exe have to do with SHLWAPI? Tom "roxioxoto" <coco002@gmail.com> wrote in message news:1154971834.120105.234410@i42g2000cwa.googlegroups.com... > Looking a function to do that. > shlwapi.lib > Sorry :) Im just trying to get c:\1\2\ or c:\1\2 >From this path c:\1\2\filename.ordir using SHLWAPI lib or s...

Documentation about CRM 1.2 db structure
Does exist any documentation about CRM 1.2 modules and related underlying SQL tables? Does exist any documentation about mapping between CRM 1.2 SQL tables and CRM 3.0 SQL tables? Many thanks If you install data migration tool, there are some excel spreadsheet contains the fields for the given entity. I don't know if that's what you are looking for. In terms of the SQL table, CRM 1.2 and CRM 3.0 is similar. All the custom attribute will be in the extension table. E.g. AccountBase, AccountExtension. Hope this helps. Darren Liu Crowe Chizek and Company http://www.crowecrm.com On ...

Unable to edit formulae
I've been trying to edit formulae on my machine, especially VLookups, but for some reason, they are appearing with small boxes instead of commas, which means I can't edit them. Any ideas what's happening? Does this mean that when you're changing an existing formula you see the boxes? Or do you see the boxes when you're typing a formula from scratch? I'm gonna guess the first. There's a windows setting that could be messed up. Open up the regional settings applet under control panel in winXP: windows start button|settings|control panel|regional and language op...

SumProduct with date and time
I have finally found that with my formulas the calulations are based on the time. Can someone help me find the best way to correct this without using macros to strip the time out of the data. Bob 09/08/2007 01:05 13/08/2007 00:00 Bob Bill 10/08/2007 23:39 Bill Kyle 11/08/2007 07:36 Kyle Bob 16/07/2007 20:39 Bill 27/07/2007 19:59 Kyle 18/07/2007 16:51 Bob 14/07/2007 16:31 Bill 21/06/2007 16:46 Kyle 30/06/2007 16:55 Bob 12/06/2007 01:05 Bill 13/06/2007 23:39 1 2 0 1 1 Kyle 14/06/2007 07:36 1 1 1 1 1 Bob 12/01/2007 20:39 1 1 2 0 1 Bill 13/08/2006 19:59 Kyle 14/05/1999 16:51 These are t...

TO CREATE A LINE CHART WITH RATE IN Y AXIS & REAL TIME IN X AXIS
i want to create a line chart in excel for rate vs time as y & x axis data. the chart should be an automatic charting as, when the particular time (10,10.30,11,11.30 ...)of system time reaches the data in one cell (rate ) should be taken automatically for charting the chart for 10am to 11.30pm. the only thing is how to check logically if the system time reached or not. Put the times in one column and the rates in the next, then create an XY chart. I can't tell a lot from your post, but if you expect Excel to determine a rate from the time, it will not. You have to write some fo...

Formula #33
Hi Can a formula in a cell be protected, even if the contents that the forula has looked is deleted. Thanks in advance Jason The cell with the formula can be locked. But that means that when the worksheet is protected, the user can't change the formula--or overwrite the cell with something else. But this doesn't mean that the cell's value will always be the same. If you change the values in the cells that this formula uses, then the formula will reevaluate and display something else. If you want to "lock-in" that value in any cell, you'll have to convert that ce...