Did you mean MOD(...,1), rather than MOD(...,24), Ron?
--
David Biddulph

"Ron Coderre" wrote in message
...
With
A1: (a time value)

Maybe this:
B1: =IF(MOD(A1-TIME(7,,),24)<TIME(11,,),"Day","Night")

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"kbee" wrote in message
...
I need to convert the data from a cell that contains date and time to a
different cell that will give a result of day /night, which functions
should
I use and what will be their syntax?
if the hours i refere to as day are 7:00AM-18:00PM, and night
18:00Pm-07:00AM
thank you

Hmmmm....Yes, I didn't notice the typo.
Yet, all of my tests returned correct values.

I probably missed a test instance where the 24 wouldn't have worked.
Yup. It can fail if the date is yesterday.

Corrected formula:
B1: =IF(MOD(A1-TIME(7,,),1)<TIME(11,,),"Day","Night")

Thanks!

--------------------------

Best Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Did you mean MOD(...,1), rather than MOD(...,24), Ron?
--
David Biddulph

"Ron Coderre" wrote in message
...
With
A1: (a time value)

Maybe this:
B1: =IF(MOD(A1-TIME(7,,),24)<TIME(11,,),"Day","Night")

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"kbee" wrote in message
...
I need to convert the data from a cell that contains date and time to a
different cell that will give a result of day /night, which functions
should
I use and what will be their syntax?
if the hours i refere to as day are 7:00AM-18:00PM, and night
18:00Pm-07:00AM
thank you

Thank you, it worked , i would appreciate if you explained what each
value/name stands for, if u could.thanx.
best regards,
kbee


"Ron Coderre" wrote:

Hmmmm....Yes, I didn't notice the typo.
Yet, all of my tests returned correct values.

I probably missed a test instance where the 24 wouldn't have worked.
Yup. It can fail if the date is yesterday.

Corrected formula:
B1: =IF(MOD(A1-TIME(7,,),1)<TIME(11,,),"Day","Night")

Thanks!

--------------------------

Best Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Did you mean MOD(...,1), rather than MOD(...,24), Ron?
--
David Biddulph

"Ron Coderre" wrote in message
...
With
A1: (a time value)

Maybe this:
B1: =IF(MOD(A1-TIME(7,,),24)<TIME(11,,),"Day","Night")

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"kbee" wrote in message
...
I need to convert the data from a cell that contains date and time to a
different cell that will give a result of day /night, which functions
should
I use and what will be their syntax?
if the hours i refere to as day are 7:00AM-18:00PM, and night
18:00Pm-07:00AM
thank you

Question_1: How does the formula work

Answer: Regarding: =IF(MOD(A1-TIME(7,,),1)<TIME(11,,),"Day","Night")

According to the rules:
The DAY category includes times that are =7AM and <6PM
The NIGHT category includes times that are =6PM and <7AM

That puts the DAY category in the middle range of times:
Actual Time:
00_01_02_03_04_05_06_07_08_09_10_11_12_13_14_15_16 _17_18_19_20_21_22_23
N__N__N__N__N__N__D__D__D__D__D__D__D__D__D__D__N_ _N__N__N__N__N__N__N

and complicates the formula by having it check
if the value is "between" 2 times.

By subtracting 7 hours from the time, we only need to test if
the time is less than 11AM (for DAY)

Actual Time:
07_08_09_10_11_12_13_14_15_16_17_18_19_20_21_22_23 _24_01_02_03_04_05_06

Adjusted Time:
00_01_02_03_04_05_06_07_08_09_10_11_12_13_14_15_16 _17_18_19_20_21_22_23
D__D__D__D__D__D__D__D__D__D__N__N__N__N__N__N__N_ _N__N__N__N__N__N__N

To Excel....
DATES are the number of days since 31-DEC-1899.
1 = 01-JAN-1900
39,492 = 14-FEB-2008

TIMES are decimal fractions of a day
Noon = 0.5 (12hrs/24hrs)

Noon on 14-FEB-2008 is: 39,492.5

Since we are only testing time, and not date, we use the MOD function
to remove the integer part of the date/time....leaving only the time.

So....the formula: =IF(MOD(A1-TIME(7,,),1)<TIME(11,,),"Day","Night")

.. Starts with the actual date/time: A1
.. Subtracts 7 hours from that value: A1-TIME(7,,)
.. Removes the date component: MOD(A1-TIME(7,,),1)..AND..fix negative values.
.. Tests if that adjusted time is less than 11AM
.. If YES...Day, otherwise...Night.

Question_2: If I just use Times, with without dates, how does the formula
change.
Answer: It doesn't.

Here's why:
If the Time is 3AM, subtracting 7 hours returns
a negative number, which cannot be a time....so we still need to MOD
function to fix that issue:

=(3AM-7AM)
= (3/24-7/24)
= (0.125 - 0.291666666666667)
= -0.166666666666667
MOD(-0.166666666666667, 1) returns 0.833333333333333
which is 8PM.

I hope that helps.

-------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)



"kbee" wrote in message
...

Thank you, it worked , i would appreciate if you explained what each
value/name stands for, if u could.thanx.
best regards,
kbee


"Ron Coderre" wrote:

Hmmmm....Yes, I didn't notice the typo.
Yet, all of my tests returned correct values.

I probably missed a test instance where the 24 wouldn't have worked.
Yup. It can fail if the date is yesterday.

Corrected formula:
B1: =IF(MOD(A1-TIME(7,,),1)<TIME(11,,),"Day","Night")

Thanks!

--------------------------

Best Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Did you mean MOD(...,1), rather than MOD(...,24), Ron?
--
David Biddulph

"Ron Coderre" wrote in message
...
With
A1: (a time value)

Maybe this:
B1: =IF(MOD(A1-TIME(7,,),24)<TIME(11,,),"Day","Night")

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"kbee" wrote in message
...
I need to convert the data from a cell that contains date and time to
a
different cell that will give a result of day /night, which functions
should
I use and what will be their syntax?
if the hours i refere to as day are 7:00AM-18:00PM, and night
18:00Pm-07:00AM
thank you

A couple typos:

Correction_1:

Actual Time:
00_01_02_03_04_05_06_07_08_09_10_11_12_13_14_15_16 _17_18_19_20_21_22_23
N__N__N__N__N__N__N__D__D__D__D__D__D__D__D__D__D_ _N__N__N__N__N__N__N

(6AM should be "N"...and wasn't)

~~~~~~~~~~~~~~~~~~~~~~~~~~
Correction_2:

Adjusted Time:
00_01_02_03_04_05_06_07_08_09_10_11_12_13_14_15_16 _17_18_19_20_21_22_23
D__D__D__D__D__D__D__D__D__D__D__N__N__N__N__N__N_ _N__N__N__N__N__N__N

(10AM should be "D"...and wasn't)

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"Ron Coderre" wrote in message
...
Question_1: How does the formula work

Answer: Regarding: =IF(MOD(A1-TIME(7,,),1)<TIME(11,,),"Day","Night")

According to the rules:
The DAY category includes times that are =7AM and <6PM
The NIGHT category includes times that are =6PM and <7AM

That puts the DAY category in the middle range of times:
Actual Time:
00_01_02_03_04_05_06_07_08_09_10_11_12_13_14_15_16 _17_18_19_20_21_22_23
N__N__N__N__N__N__D__D__D__D__D__D__D__D__D__D__N_ _N__N__N__N__N__N__N

and complicates the formula by having it check
if the value is "between" 2 times.

By subtracting 7 hours from the time, we only need to test if
the time is less than 11AM (for DAY)

Actual Time:
07_08_09_10_11_12_13_14_15_16_17_18_19_20_21_22_23 _24_01_02_03_04_05_06

Adjusted Time:
00_01_02_03_04_05_06_07_08_09_10_11_12_13_14_15_16 _17_18_19_20_21_22_23
D__D__D__D__D__D__D__D__D__D__N__N__N__N__N__N__N_ _N__N__N__N__N__N__N

To Excel....
DATES are the number of days since 31-DEC-1899.
1 = 01-JAN-1900
39,492 = 14-FEB-2008

TIMES are decimal fractions of a day
Noon = 0.5 (12hrs/24hrs)

Noon on 14-FEB-2008 is: 39,492.5

Since we are only testing time, and not date, we use the MOD function
to remove the integer part of the date/time....leaving only the time.

So....the formula: =IF(MOD(A1-TIME(7,,),1)<TIME(11,,),"Day","Night")

. Starts with the actual date/time: A1
. Subtracts 7 hours from that value: A1-TIME(7,,)
. Removes the date component: MOD(A1-TIME(7,,),1)..AND..fix negative
values.
. Tests if that adjusted time is less than 11AM
. If YES...Day, otherwise...Night.

Question_2: If I just use Times, with without dates, how does the formula
change.
Answer: It doesn't.

Here's why:
If the Time is 3AM, subtracting 7 hours returns
a negative number, which cannot be a time....so we still need to MOD
function to fix that issue:

=(3AM-7AM)
= (3/24-7/24)
= (0.125 - 0.291666666666667)
= -0.166666666666667
MOD(-0.166666666666667, 1) returns 0.833333333333333
which is 8PM.

I hope that helps.

-------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)



"kbee" wrote in message
...

Thank you, it worked , i would appreciate if you explained what each
value/name stands for, if u could.thanx.
best regards,
kbee


"Ron Coderre" wrote:

Hmmmm....Yes, I didn't notice the typo.
Yet, all of my tests returned correct values.

I probably missed a test instance where the 24 wouldn't have worked.
Yup. It can fail if the date is yesterday.

Corrected formula:
B1: =IF(MOD(A1-TIME(7,,),1)<TIME(11,,),"Day","Night")

Thanks!

--------------------------

Best Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Did you mean MOD(...,1), rather than MOD(...,24), Ron?
--
David Biddulph

"Ron Coderre" wrote in message
...
With
A1: (a time value)

Maybe this:
B1: =IF(MOD(A1-TIME(7,,),24)<TIME(11,,),"Day","Night")

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"kbee" wrote in message
...
I need to convert the data from a cell that contains date and time to
a
different cell that will give a result of day /night, which
functions
should
I use and what will be their syntax?
if the hours i refere to as day are 7:00AM-18:00PM, and night
18:00Pm-07:00AM
thank you

Hi Ron,
your explanation was great, i tried it but i am novice at thiis and i keep
messing up on the hour , if cell a1=13:00 Pm and cella2= 18:00Pm , i want it
to show in cell b1=day and cell b2=night. i put the mod with the a1-7/24 but
it did not result correctly, i must do something wrong, my last request for
the syntax for it.
thanks,
bee

"Ron Coderre" wrote:

A couple typos:

Correction_1:

Actual Time:
00_01_02_03_04_05_06_07_08_09_10_11_12_13_14_15_16 _17_18_19_20_21_22_23
N__N__N__N__N__N__N__D__D__D__D__D__D__D__D__D__D_ _N__N__N__N__N__N__N

(6AM should be "N"...and wasn't)

~~~~~~~~~~~~~~~~~~~~~~~~~~
Correction_2:

Adjusted Time:
00_01_02_03_04_05_06_07_08_09_10_11_12_13_14_15_16 _17_18_19_20_21_22_23
D__D__D__D__D__D__D__D__D__D__D__N__N__N__N__N__N_ _N__N__N__N__N__N__N

(10AM should be "D"...and wasn't)

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"Ron Coderre" wrote in message
...
Question_1: How does the formula work

Answer: Regarding: =IF(MOD(A1-TIME(7,,),1)<TIME(11,,),"Day","Night")

According to the rules:
The DAY category includes times that are =7AM and <6PM
The NIGHT category includes times that are =6PM and <7AM

That puts the DAY category in the middle range of times:
Actual Time:
00_01_02_03_04_05_06_07_08_09_10_11_12_13_14_15_16 _17_18_19_20_21_22_23
N__N__N__N__N__N__D__D__D__D__D__D__D__D__D__D__N_ _N__N__N__N__N__N__N

and complicates the formula by having it check
if the value is "between" 2 times.

By subtracting 7 hours from the time, we only need to test if
the time is less than 11AM (for DAY)

Actual Time:
07_08_09_10_11_12_13_14_15_16_17_18_19_20_21_22_23 _24_01_02_03_04_05_06

Adjusted Time:
00_01_02_03_04_05_06_07_08_09_10_11_12_13_14_15_16 _17_18_19_20_21_22_23
D__D__D__D__D__D__D__D__D__D__N__N__N__N__N__N__N_ _N__N__N__N__N__N__N

To Excel....
DATES are the number of days since 31-DEC-1899.
1 = 01-JAN-1900
39,492 = 14-FEB-2008

TIMES are decimal fractions of a day
Noon = 0.5 (12hrs/24hrs)

Noon on 14-FEB-2008 is: 39,492.5

Since we are only testing time, and not date, we use the MOD function
to remove the integer part of the date/time....leaving only the time.

So....the formula: =IF(MOD(A1-TIME(7,,),1)<TIME(11,,),"Day","Night")

. Starts with the actual date/time: A1
. Subtracts 7 hours from that value: A1-TIME(7,,)
. Removes the date component: MOD(A1-TIME(7,,),1)..AND..fix negative
values.
. Tests if that adjusted time is less than 11AM
. If YES...Day, otherwise...Night.

Question_2: If I just use Times, with without dates, how does the formula
change.
Answer: It doesn't.

Here's why:
If the Time is 3AM, subtracting 7 hours returns
a negative number, which cannot be a time....so we still need to MOD
function to fix that issue:

=(3AM-7AM)
= (3/24-7/24)
= (0.125 - 0.)
= -0.
MOD(-0., 1) returns 0.
which is 8PM.

I hope that helps.

-------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)



"kbee" wrote in message
...

Thank you, it worked , i would appreciate if you explained what each
value/name stands for, if u could.thanx.
best regards,
kbee


"Ron Coderre" wrote:

Hmmmm....Yes, I didn't notice the typo.
Yet, all of my tests returned correct values.

I probably missed a test instance where the 24 wouldn't have worked.
Yup. It can fail if the date is yesterday.

Corrected formula:
B1: =IF(MOD(A1-TIME(7,,),1)<TIME(11,,),"Day","Night")

Thanks!

--------------------------

Best Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Did you mean MOD(...,1), rather than MOD(...,24), Ron?
--
David Biddulph

"Ron Coderre" wrote in message
...
With
A1: (a time value)

Maybe this:
B1: =IF(MOD(A1-TIME(7,,),24)<TIME(11,,),"Day","Night")

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"kbee" wrote in message
...
I need to convert the data from a cell that contains date and time to
a
different cell that will give a result of day /night, which
functions
should
I use and what will be their syntax?
if the hours i refere to as day are 7:00AM-18:00PM, and night
18:00Pm-07:00AM
thank you

sorry, but how do I change it if i decided to use just the hour AM/PM
without the m/d/y?
thnx again

"Ron Coderre" wrote:

Hmmmm....Yes, I didn't notice the typo.
Yet, all of my tests returned correct values.

I probably missed a test instance where the 24 wouldn't have worked.
Yup. It can fail if the date is yesterday.

Corrected formula:
B1: =IF(MOD(A1-TIME(7,,),1)<TIME(11,,),"Day","Night")

Thanks!

--------------------------

Best Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Did you mean MOD(...,1), rather than MOD(...,24), Ron?
--
David Biddulph

"Ron Coderre" wrote in message
...
With
A1: (a time value)

Maybe this:
B1: =IF(MOD(A1-TIME(7,,),24)<TIME(11,,),"Day","Night")

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"kbee" wrote in message
...
I need to convert the data from a cell that contains date and time to a
different cell that will give a result of day /night, which functions
should
I use and what will be their syntax?
if the hours i refere to as day are 7:00AM-18:00PM, and night
18:00Pm-07:00AM
thank you