Get time from a Date & Time field

[Duncs]

I have two cells that contain a date & time, and are formatted as “ddd
dd/mm/yyyy hh:mm”.

What I need to do is put a formula in another cell that will display a
‘Y’ if the time in the first cell is after 08:00 and before 17:00.
Can I do this, and if so how?

 

[Pete_UK]

You can split the date part off by using this:

=INT(A1)

and the time part off by this:

=MOD(A1,1)

or this:

=A1 - INT(A1)

assuming the date/time is in cell A1.

So, to do what you want with one formula you can try this:

=IF(AND(MOD(A1,1)<17/24,MOD(A1,1)>8/24),"Y","N")

Leaving the fractions as 17/24 and 8/24 makes it a bit easier to
understand.

Hope this helps.

Pete

 

[Duncs]

Thanks for that Pete. I have a couple of other time calculation to
do, similar to this, so this has given me a starting point. I may be
back on if I get stuck though!

Cheers

Duncs

 

[Pete_UK]

You're welcome, Duncs - thanks for feeding back.

Pete

 

[Duncs]

Pete,

Hope you don't mind these further questions...

On the back of what you provided below, I have now been asked to
expand on what I have at the moment. The idea is as follows:

The spreadsheet has a start and end date / time. If:

1. The start & end dates are the same day, and the start & end times
are between 08:00 & 17:00, a 1 should be entered in the field
IF(AND(MOD(H2,1)<17/24,MOD(G2,1)>8/24),"1","N")
2. If the start time is between 08:00 & 17:00, but the end time is
after 17:00 but before 08:00 the following morning, a 2 should be
entered in the field
IF(AND(MOD(G3,1)>8/24,MOD(H3,1)>17/24),"2","N")
3. If the start time is after 17:00 but before 08:00 the next day, and
end time is between 08:00 & 17:00, a 3 should be entered in the field
IF(AND(MOD(G4,1)>17/24,MOD(H4,1)<24/24),"3","N")
4. If the start time is after 17:00 on a Friday and the end time is
before 08:00 on the Monday, a 4 should be entered in the field
IF(AND(WEEKDAY(G5, 2)>=6, WEEKDAY(H5, 2)<=7), 4, 0)
5. If the start time is after 17:00 and the end time is before 08:00
the following day, a 5 should entered in the field
IF(AND(WEEKDAY(G6, 2)>=6, WEEKDAY(H6, 2)<=7), 5, 0)

Complicated!!

I don't know if the formulae I've listed are right, so perhaps you
could look them over. What I'm now worried about is combining them
all into the one cell. If I combine them, I get:

=IF(AND(MOD(H2,1)<17/24,MOD(G2,1)>8/24),"1",IF(AND(MOD(G3,1)>8/24,MOD(H3,1)>17/24),"2",IF(AND(MOD(G4,1)>17/24,MOD(H4,1)<24/24),"3",IF(AND(WEEKDAY(G5,
2)>=6, WEEKDAY(H5, 2)<=7), 4, IF(AND(WEEKDAY(G6, 2)>=6, WEEKDAY(H6,
2)<=7), 5, "")))))

However, this can't be right. When I have:

Start End
Fri 01/01/10 12:15 Fri 01/01/10 19:00

This gives a result of 4, "If the start time is after 17:00 on a
Friday and the end time is before 08:00 on the Monday", when it should
be 2, "If the start time is between 08:00 & 17:00, but the end time is
after 17:00 but before 08:00 the following morning"

Can you cast your eye over things and let me know what I'm doing
wrong?

Many thanks

Duncs

 

[Pete_UK]

Your combined formula is looking at different cells each time - you
will need to look at the same cells (G2 and H2).

I'll take another look at it this afternoon.

Pete

 

[Duncs]

Sorry Pete. I'd copied teh cell contents and pasted it nto the main
cell. Doh!

Still, it doesn't give me the results I would expect.

Duncs

 

[Duncs]

Sorry Pete, I mucked up there. I was testing somne different things
and copied and pasted teh cells into the one formula. This obviously
didn't change the cell references, so the reason for the different
cells.

 

[Duncs]

Pete,

One more things i've been asked to do, which I'd appreciate a hint
frmo you on how to do...

As I said before, times are entered as Start / End, which includes a
date & time value. The working weeks is split as follows:

08:00 to 17:00 Monday -> Friday, normal support hours
17:00 to 08:00 Monday -> Friday, out of hours
17:00 Friday to 08:00 Monday, weekend support

I need ot show the number of hours out of hours worked, as well as the
number of hours weekend working. So...

Start Time End Time Hours / Type
Tuesday 09:00 Tuesday 16:00
Wednesday 10:15 Wednesday 18:15 01:15 o/h
Monday 06:45 Monday 10:45 01:15 o/h
Thursday 05:15 Friday 13:45 17:45 o/h

Also, if the support starts at any time after 17:00 on Friday and ends
on the Monday any time up until 08:00, the total time is shown as
weekend time and shoul duse the end time of 08:00 or actual end time,
whichever is lower.

Can you give me a hint on this?

Many TIA

Duncs

 

[joeu2004]

I have two cells that contain a date & time, and are formatted as “ddd
dd/mm/yyyy hh:mm”.

What I need to do is put a formula in another cell that will display a
‘Y’ if the time in the first cell is after 08:00 and before 17:00.

You can split [...] the time part off by this:
=MOD(A1,1) [....]
So, to do what you want with one formula you can try this:
=IF(AND(MOD(A1,1)<17/24,MOD(A1,1)>8/24),"Y","N")

Caveat emptor.... Pete's formula is unreliable because of anomalies
with binary floating-point arithmetic. Specially, the fractional part
of a date/time value is often significantly different from the
fractional part of the time value alone, which 17/24 and 8/24
represent.

It would be more reliable to write:
=IF(AND("08:00"<TEXT(A1,"hh:mm"),TEXT(A1,"hh:mm")<"17:00"),"Y","N")

PS: For broader participation, you might want to post future
inquiries using the MS Answers Forums at
http://social.answers.microsoft.com/Forums/en-US/category/officeexcel.
It's not that I like that forum. It's just that MS has ceased to
support the Usenet newsgroups. Hence, participation here is limited
to the sites that share a common newsgroup mirror, which is no longer
centralized at MS.

 

[joeu2004]

PS....

[....]
It would be more reliable to write:
=IF(AND("08:00"<TEXT(A1,"hh:mm"),TEXT(A1,"hh:mm")<"17:00"),"Y","N")

On second thought, Pete's formula is sufficient if you truly want
"after" 8am and "before" 5pm.

I ass-u-me you will eventually realize that you want the range to be
inclusive, changing "<" to "<=".

 

[Pete_UK]

Hi Duncs,

take a look at this thread from a few years ago:

http://groups.google.com/group/micr...n+time_split+insubject:*excel*+author:Pete_uk

It was to do with allocating production to different shifts based on
the hours within each shift. Although it is not exactly the same as
your problem, it strikes me that you might be able to amend the
approach.

I went on to develop this further to apply to long-duration telephone
calls, splitting the time between different charging periods, and that
certainly seems to be similar to what you want to do - if only I could
find the file !! <bg>

Hope this helps.

Pete

 

[Duncs]

Cheers Pete. I'll have a look at the post.

As per usual, when you show someone what they asked for, they then go
on to tell you that this isn't really what they wanted. They wanted
more functionality, hence the addiitonal checks!

Once again, my many thanks to you and to joeu2004 for your help.

Duncs

 

[Pete_UK]

I found the file I was referring to above as soon as I sent off that
post. What I did for the telephone call time split was to divide the
week up into 21 time bands, along these lines:

time: 0 8 18 24
Mon 1 2 3
Tue 4 and so on
Wed
Thu
Fri
Sat
Sun 19 20 21

The UDF then calculated the start band and repeatedly subtacted time
and allocated it to the appropriate band until the duration came down
to zero. Then the times from the 21 time bands were added up to give
the elapsed time in the three charging periods. It differed from yours
slightly, in that with phone data you have the start date and time and
then the duration (in seconds), whereas you have start and end dates
and times, but the same principles apply.

Let me know if you would like to see the UDF, or if you would like it
to be modified to suit your data exactly.

Pete

 

[Duncs]

Pete,

The UDF would be great. I'll have a play about with it, rather than
asking you to do it for me...you may enjoy doing, and be quite happy,
but I won't really learn anything! ( I may post some questions back
on it...or admit defeat! )

Thansk again for your help.

Duncs

 

[Pete_UK]

Duncs,

here's the UDF:

Function time_split(start_date, start_time, duration, sh) As Long
'Split elapsed time into three periods or 21 shifts
'Pete Ashurst: March 06 2006
On Error GoTo Failed:
'Initialise variables
Dim start(1 To 21) As Long
Dim shift(1 To 21) As Long
Dim period(1 To 3) As Long
Dim start_period As Integer
Dim my_start As Long
Dim time_rem As Long
Dim i As Integer
For i = 1 To 21
shift(i) = 0
If ((i - 1) Mod 3) = 0 Then start(i) = 0 '00:00:00
If ((i - 1) Mod 3) = 1 Then start(i) = 28800 '08:00:00
If ((i - 1) Mod 3) = 2 Then start(i) = 64800 '18:00:00
Next i
For i = 1 To 3
period(i) = 0
Next i
'Exit function if negative duration
If duration <= 0 Then time_split = 0: Exit Function
'Allocate the starting shift (i)
If start_time < TimeValue("08:00:00") Then
start_shift = 1
ElseIf start_time < TimeValue("18:00:00") Then
start_shift = 2
Else
start_shift = 3
End If
'Monday 00:00:00 is first shift of 21
i = (Weekday(start_date, vbMonday) - 1) * 3 + start_shift
my_start = start_time * 86400 '24 hours
'Start splitting the duration between shifts
Do Until duration <= 0
'Evaluate remaining time to start of next shift
time_rem = start(((i + 21) Mod 21) + 1) - my_start
'Adjust remaining time for day wrap-around
If time_rem < 0 Then time_rem = time_rem + 86400
'Increase this shift's duration, reduce overall duration
If duration > time_rem Then
shift(i) = shift(i) + time_rem
duration = duration - time_rem
'Get ready for next shift
i = ((i + 21) Mod 21) + 1
my_start = start(i)
Else
'Final part added to appropriate shift
shift(i) = shift(i) + duration
duration = 0
End If
Loop
'Allocate shift time to normal periods
For i = 1 To 21
If i > 15 Then
period(3) = period(3) + shift(i)
ElseIf i = 2 Or i = 5 Or i = 8 Or i = 11 Or i = 14 Then
period(1) = period(1) + shift(i)
Else
period(2) = period(2) + shift(i)
End If
Next i
'Return appropriate period time-split
time_split = period(sh)
Exit Function
Failed:
time_split = CVErr(xlErrValue)
End Function

Hopefully the comments in it will help you to follow what's happening.

I had a start date in D3 and a time in E3, with a duration (in
seconds) in G3. The following formula were placed in the cells stated:

M3: =time_split($D3,$E3,$G3,1)
N3: =time_split($D3,$E3,$G3,2)
O3: =time_split($D3,$E3,$G3,3)

and these returned the appropriate seconds duration in each of the
charging periods - peak period (col M) was 8:00am to 6:00pm Monday to
Friday, off-peak (col N) was before 8:00am and after 6:00pm Monday to
Friday, and Weekend rate (col O) was any time on Saturday and Sunday.
Yours are slightly different, in that shift 1 and shift 18 (the time
bands) are considered as part of the weekend.

Well, see how you get on with this, and you can always post back if
you have any problems adapting it to your situation.

Hope this helps.

Pete