Extract Date/Time to Date and Time

**ernestgoh** · 01-09-2010, 08:03 AM

Hi have this simple problem but I do not know the solution.

In Cell A1 = 01/12/2008 12:00:00 AM

I am trying to extract in cell A2 = 01/12/2008 and A3 = 12:00:00 AM.

But I will end up having a string of numbers each time I try to right (A1,11).

My end result is I will use the values in A3 if between 12am to 5am, assign a value in A4 as "Night Shift"

A1 = 01/12/2008 12:00:00AM
A2 = 01/12/2008
A3 = 12:00:00Am
A4 = if(And(A3>12,A3<5),"Night Shift","Day Shift")

Thanks in advance for helping!

**daddylonglegs** · 01-09-2010, 08:34 AM

You can extract the date from a date and time with

=INT(A1)

and the time with

=MOD(A1,1)

but you could do everything without the intermediate step if you want, i.e.

=if(And(MOD(A1,1)>"12:00"+0,MOD(A1,1)<"17:00"+0),"Night Shift","Day Shift")

**Marcol** · 01-09-2010, 09:18 AM

Sorry - This dosn't work I'll have another try!
Daddylonglegs is absolutely correct
You can't simply use Format Cells

Range "A1" format cells Custom dd/mm/yyyy hh:mm AM/PM
Range "A2" format cells Date *14/03/2001
Range "A2" =A1
Range "A3" format cells Time 1:30:55 PM
Range "A3" =A1
Range "A4" format cells General
Range "A4" = IF(AND(A3>12,A3<5),"Night Shift","Day Shift")

Might be easier to use Columns rather than Rows in your sheet.
ie A2 becomes B1, A3 becomes C1, A4 becomes D1
That way you can format each column, then use Fill Down for B1 to D1
as required.

Only a thought.

**daddylonglegs** · 01-09-2010, 09:27 AM

Hello Marcol,

The problem with using formatting is that it only changes the display it doesn't change the underlying value.

It seems to me that ernestgoh wants to extract the time so that he can evaluate it in his IF function. If A3 contains just the same value as A1, only formatted to look different, then that won't work......

**Marcol** · 01-09-2010, 11:59 AM

Sorry for earlier letting my mouth run before my brain was in gear!
Further to Daddylonglegs solution

I found this formula helped to explain how it works with the 24hr clock.

= IF(AND(MOD(A1,1)*24>12,MOD(A1,1)*24<17),"Day Shift","Night Shift")

This can be expanded if required as follows

If you add Day Shift Start Time in "A2"
and Day Shift End Time in "A3"

Then

= IF(AND(MOD(A1,1)>MOD(A2,1),MOD(A1,1)<MOD(A3,1)),"Day Shift","Night Shift")

Hope this might help you and cool my red face a little.

**daddylonglegs** · 01-09-2010, 12:31 PM

Originally Posted by ernestgoh

My end result is I will use the values in A3 if between 12am to 5am, assign a value in A4 as "Night Shift"

That looks like a better approach Marcol but I fear we've both misinterpreted a little based on the above quote. I assumed 12 noon to 5pm, I'm not sure why..... but from the above it appears that "Night shift" would be returned if the time from A1 is any time before 5 AM, in which case you don't need to check for "after midnight" as every time will fulfill that (unless you need to make a specific case for exactly 12:00) so the solution's a little simpler, i.e. either

=IF(MOD(A1,1)*24<5,"Night Shift","Day Shift")

or even use the HOUR function like this

=IF(HOUR(A1)<5,"Night Shift","Day Shift")

**ernestgoh** · 01-09-2010, 02:53 PM

thanks guys! it works for me. the hour function is my favorite!

thanks again.

**Marcol** · 01-09-2010, 02:49 PM

Thanks Daddylonglegs.

I missed this

My end result is I will use the values in A3 if between 12am to 5am, assign a value in A4 as "Night Shift"

And used this as posted not noticing that 12 should have read 0 and assumed 5 to be 17

A4 = if(And(A3>12,A3<5),"Night Shift","Day Shift")

The dangers of using AM/PM instead of the 24hr system?

I did wonder why I had to reverse the If conditions.

Again you are correct, assuming night shift never begins before midnight.