iwhich funvtion to use : in conversion of date and hour to day and

[Ron Coderre]

First:
As David Biddulph astutely pointed out....
there was a flaw in my original formula.

This formula: =IF(MOD(A1-TIME(7,,),24)<TIME(11,,),"Day","Night")
Should be: =IF(MOD(A1-TIME(7,,),1)<TIME(11,,),"Day","Night")

Notice that the number 24 has been replaced with the number 1.
But, that wouldn't impact your latest scenario:

Try this:
A1: 1:00:00 PM
A2: 6:00:00 PM

B1: =IF(MOD(A1-TIME(7,,),1)<TIME(11,,),"Day","Night")
Copy B1 into B2

The results are:
B1 returns Day
B2 returns Night

Does that help?
Post back if you have more questions.
--------------------------

Regards,

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

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

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)

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)




Thank you, it worked , i would appreciate if you explained what each
value/name stands for, if u could.thanx.
best regards,
kbee
(e-mail address removed)

:

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

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)

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

 

[kbee]

Thiis is really strange, I tried your formula on my old table, yesterday and
it worked, today i built a new one however it gave the result of a value and
yet in a different table the cell just contains the formula??? how is it
possible, i check the formatings and they are identical??? what could cause
the problem???
bee again

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

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)

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)




Thank you, it worked , i would appreciate if you explained what each
value/name stands for, if u could.thanx.
best regards,
kbee
(e-mail address removed)

:

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

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)

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

 

[Ron Coderre]

Well, there could be a few reasons.

First, from the Main Menu: <tools><options><view tab>....UNcheck: Formulas.
Second, set the number format of the cell to General
Third, select the cell, press [F2], then press [ENTER]

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

Regards,

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

Thiis is really strange, I tried your formula on my old table, yesterday
and
it worked, today i built a new one however it gave the result of a value
and
yet in a different table the cell just contains the formula??? how is it
possible, i check the formatings and they are identical??? what could
cause
the problem???
bee again

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

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)


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)




Thank you, it worked , i would appreciate if you explained what each
value/name stands for, if u could.thanx.
best regards,
kbee
(e-mail address removed)

:

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

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)

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