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

[kbee]

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]

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)

 

[Tyro]

No conversion is necessary. Copy the cells and format the cells as time or
format the cells as time in place. For example, if your date/time cell is
A1, then in B1 put =A1 and format B1 as time or simply format A1 as time.
You can also format the cells as date/time.

Tyro

 

[Pete_UK]

This isn't what the OP asked for - he/she wanted the words Day or
Night in one column dependent on the time in another column: Day is
between 7:00 and 18:00, and Night is between 18:00 and 7:00

Ron's formula seems to do this (though I haven't tested it).

Pete

 

[Tyro]

First of all it is not 18:00PM. Miltary (24 hour time) does not use AM, PM.
If you want the hours between 7:00 and 18:00 to be day and the rest night,
and A1 contains the date and time, the it's simply

=IF(AND(MOD(A1,1)>=VALUE("7:00"),MOD(A1,1)<=VALUE("18:00")),"Day","Night")
or
=IF(AND(MOD(A1,1)>=VALUE("7:00 AM"),MOD(A1,1)<=VALUE("6:00
PM")),"Day","Night")

I find that easy to understand

Tyro

 

[Teethless mama]

=IF(A1>=TIME(18,,),"night","day")

 

[Tyro]

It's interesting to note that with your formula, 12:01:00 AM returns Day.
Where I live in the USA, it is dark at that time. <g>

Tyro

 

[kbee]

why do u use mod?
cell a1=1/1/08 08:00:00 AM, cell a2= 1/1/08 22:00:00 PM
cell b1 or b2= day/night
what should be the syntax?
thanks to anyone who can help with this.

 

[Tyro]

Dates in Excel are maintained as whole numbers. Jan 1, 1900 is day 1, Feb
12, 2008 is day 39,340 and Excel's highest date, Dec 31, 9999 is day
2,948,465.
Times in Excel are maintained as decimal fractions of 24 hours. So,
12:00AM, midnight, is 0.000000, 12:01 AM, 1 minute after midnight is
0.000694. (1/(24*60)
1am is 1/24 = 0.041667. 12PM noon is 12/25 = .5 so noon on Feb 12, 2008 is:
39340.5 If there is no date, the time is simply 0.5 Using =MOD(39340.5,1)
produces the remainder after dividing by 1, i.e. 0.5, the time portion of
the date/time. Most books on Excel explain this.

Tyro

 

[Tyro]

Correction

12/24 is noon 0.5

Tyro

Dates in Excel are maintained as whole numbers. Jan 1, 1900 is day 1, Feb
12, 2008 is day 39,340 and Excel's highest date, Dec 31, 9999 is day
2,948,465.
Times in Excel are maintained as decimal fractions of 24 hours. So,
12:00AM, midnight, is 0.000000, 12:01 AM, 1 minute after midnight is
0.000694. (1/(24*60)
1am is 1/24 = 0.041667. 12PM noon is 12/25 = .5 so noon on Feb 12, 2008
is: 39340.5 If there is no date, the time is simply 0.5 Using
=MOD(39340.5,1) produces the remainder after dividing by 1, i.e. 0.5, the
time portion of the date/time. Most books on Excel explain this.

Tyro

 

[Tyro]

I already gave you the formula. If you choose not to use it, ok!

Tyro

 

[David Biddulph]

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

 

[Ron Coderre]

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)

 

[kbee]

Thank you for your atempt but the suggestion did not yield the result, and i
dont know how to correct it.
thanx again

 

[kbee]

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)

 

[kbee]

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

 

[kbee]

the formula results: value and not d/n
how would u change it to express d/n with the original cell only includes
the "hour am/pm" ?
thanx

 

[Ron Coderre]

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)

 

[Ron Coderre]

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

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]

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