Date/time calculations 1

[matpj]

anyone,

I have two fields that are both of the format <dd/mm/yy hh:mm>

I need to perform a calculation that will determine the elapsed days and hours from one field to the next.

e.g. Start: 1/01/01 00:00 End 2/01/01 00:30
duration = 1 day and 30 minutes.

Is this at all possible in Excel?

 

[pcdaveh]

This should do the trick for you!
Post another note if you need help getting this to work for you.

Option Explicit
' From &quot;VBA Developer's Handbook&quot;
' by Ken Getz and Mike Gilbert
' Copyright 1997; Sybex, Inc. All rights reserved.

Private Declare Function GetProfileString Lib &quot;kernel32&quot; Alias &quot;GetProfileStringA&quot; _
(ByVal lpAppName As String, ByVal lpKeyName As String, _
ByVal lpDefault As String, ByVal lpReturnedString As String, ByVal nSize As Long) As Long

Function dhFormatInterval(dtmStart As Date, datend As Date, _
Optional strFormat As String = &quot;H:MM:SS&quot As String
' Return the difference between two times,
' formatted as specified in strFormat.

' From &quot;VBA Developer's Handbook&quot;
' by Ken Getz and Mike Gilbert
' Copyright 1997; Sybex, Inc. All rights reserved.

' Requires:
' GetTimeDelimiter

' In:
' dtmStart:
' starting date for the interval, including a time portion
' datend:
' ending date for the interval, including a time portion
' strFormat (optional):
' format specifier, as shown below. (Default: &quot;H:MM:SS&quot
' Out:
' Return Value:
' The formatted time difference.
' Comment:
' Due to the way the calculations are performed, the largest interval
' is 68 years or so.
' Example:
' Using #1/1/97 12:00 PM# and #1/4/97 2:45:45 PM# as the dates, and one
' of the following format templates,
' dhFormatInterval(#1/1/97 12:00 PM#, #1/4/97 2:45:45 PM#, &quot;<format>&quot
' will return (using each of the following format strings):
' D H 3 Days 3 Hours
' D H M 3 Days 2 Hours 46 Minutes
' D H M S 3 Days 2 Hours 45 Minutes 45 Seconds
' D H:MM 3 Days 2:46
' D HH:MM 3 Days 02:46
' D HH:MM:SS 3 Days 02:45:45

' H M 74 Hours 46 Minutes
' H:MM 74:46 (leading 0 on minutes, if necessary)
' H:MM:SS 74:45:45

' M S 4485 Minutes 45 Seconds
' M:SS 4485:45 (leading 0 on seconds, if necessary)

Dim lngSeconds As Long
Dim sngMinutes As Single
Dim sngHours As Single
Dim sngDays As Single

Dim intSeconds As Integer
Dim intMinutes As Integer
Dim intHours As Integer

Dim intRoundedHours As Integer
Dim intRoundedMinutes As Integer

Dim strDay As String
Dim strHour As String
Dim strMinute As String
Dim strSecond As String
Dim strOut As String

Dim lngFullDays As Long
Dim lngFullHours As Long
Dim lngFullMinutes As Long

Dim strDelim As String

' If you don't want to use the local delimiter,
' but a specific one, replace the next line with
' this:
' strDelim = &quot;:&quot;
strDelim = GetTimeDelimiter()

' Calculate the full number of seconds in the interval.
' This limits the calculation to 2 billion seconds (68 years
' or so), but that's not too bad. Then calculate the
' difference in minutes, hours, and days, as well.
lngSeconds = DateDiff(&quot;s&quot;, dtmStart, datend)
sngMinutes = lngSeconds / 60
sngHours = sngMinutes / 60
sngDays = sngHours / 24

' Get the full hours and minutes, for later display.
lngFullDays = Int(sngDays)
lngFullHours = Int(sngHours)
lngFullMinutes = Int(sngMinutes)

' Get the incremental amount of each unit.
intHours = Int((sngDays - lngFullDays) * 24)
intMinutes = Int((sngHours - lngFullHours) * 60)
intSeconds = CInt((sngMinutes - lngFullMinutes) * 60)

' In some instances, time values must be rounded.
' The next two lines depend on the fact that a true statement
' has a value of -1, and a false statement has a value of 0.
' The code needs to add 1 to the value if the following expression
' is true, and 0 if not.
intRoundedHours = intHours - (intMinutes > 30)
intRoundedMinutes = intMinutes - (intSeconds > 30)

strDay = &quot;Days&quot;
strHour = &quot;Hours&quot;
strMinute = &quot;Minutes&quot;
strSecond = &quot;Seconds&quot;

If lngFullDays = 1 Then strDay = &quot;Day&quot;
Select Case strFormat
Case &quot;D H&quot;
If intRoundedHours = 1 Then strHour = &quot;Hour&quot;
strOut = lngFullDays & &quot; &quot; & strDay & &quot; &quot; & _
intRoundedHours & &quot; &quot; & strHour
Case &quot;D H M&quot;
If intHours = 1 Then strHour = &quot;Hour&quot;
If intRoundedMinutes = 1 Then strMinute = &quot;Minute&quot;
strOut = lngFullDays & &quot; &quot; & strDay & &quot; &quot; & _
intHours & &quot; &quot; & strHour & &quot; &quot; & _
intRoundedMinutes & &quot; &quot; & strMinute
Case &quot;D H M S&quot;
If intHours = 1 Then strHour = &quot;Hour&quot;
If intMinutes = 1 Then strMinute = &quot;Minute&quot;
If intSeconds = 1 Then strSecond = &quot;Second&quot;
strOut = lngFullDays & &quot; &quot; & strDay & &quot; &quot; & _
intHours & &quot; &quot; & strHour & &quot; &quot; & _
intMinutes & &quot; &quot; & strMinute & &quot; &quot; & _
intSeconds & &quot; &quot; & strSecond

Case &quot;D H:MM&quot; ' 3 Days 2:46&quot;
strOut = lngFullDays & &quot; &quot; & strDay & &quot; &quot; & _
intHours & strDelim & Format(intRoundedMinutes, &quot;00&quot
Case &quot;D HH:MM&quot; ' 3 Days 02:46&quot;
strOut = lngFullDays & &quot; &quot; & strDay & &quot; &quot; & _
Format(intHours, &quot;00&quot & strDelim & _
Format(intRoundedMinutes, &quot;00&quot
Case &quot;D HH:MM:SS&quot; ' 3 Days 02:45:45&quot;
strOut = lngFullDays & &quot; &quot; & strDay & &quot; &quot; & _
Format(intHours, &quot;00&quot & strDelim & _
Format(intMinutes, &quot;00&quot & strDelim & _
Format(intSeconds, &quot;00&quot

Case &quot;H M&quot; ' 74 Hours 46 Minutes&quot;
If lngFullHours = 1 Then strHour = &quot;Hour&quot;
If intRoundedMinutes = 1 Then strMinute = &quot;Minute&quot;
strOut = lngFullHours & &quot; &quot; & strHour & &quot; &quot; & _
intRoundedMinutes & &quot; &quot; & strMinute
Case &quot;H:MM&quot; ' 74:46 (leading 0 on minutes, if necessary)
strOut = lngFullHours & strDelim & Format(intRoundedMinutes, &quot;00&quot
Case &quot;H:MM:SS&quot; ' 74:45:45&quot;
strOut = lngFullHours & strDelim & _
Format(intMinutes, &quot;00&quot & strDelim & _
Format(intSeconds, &quot;00&quot

Case &quot;M S&quot; ' 4485 Minutes 45 Seconds
If lngFullMinutes = 1 Then strMinute = &quot;Minute&quot;
If intSeconds = 1 Then strSecond = &quot;Second&quot;
strOut = lngFullMinutes & &quot; &quot; & strMinute & &quot; &quot; & _
intSeconds & &quot; &quot; & strSecond
Case &quot;M:SS&quot; ' 4485:45 (leading 0 on seconds, if necessary)&quot;
strOut = lngFullMinutes & strDelim & _
Format(intSeconds, &quot;00&quot

Case Else
strOut = &quot;&quot;
End Select
dhFormatInterval = strOut
End Function

Private Function GetTimeDelimiter() As String
' Retrieve the time delimiter from, believe it or not,
' WIN.INI. This is the only reasonable solution
' to this problem, even in this day and age!

' Used by:
' dhCTimeStr
' dhFormatInterval

' Requires:
' GetProfileString declaration

' From &quot;VBA Developer's Handbook&quot;
' by Ken Getz and Mike Gilbert
' Copyright 1997; Sybex, Inc. All rights reserved.

Const conMaxSize = 10
Dim strBuffer As String
Dim intLen As Integer

strBuffer = Space(conMaxSize)
intLen = GetProfileString(&quot;intl&quot;, &quot;sTime&quot;, &quot;&quot;, strBuffer, conMaxSize)
GetTimeDelimiter = Left(strBuffer, intLen)
End Function

 

[matpj]

Wow, thats a lot of code.

I didn't know it was possible to use code in Excel?!!

right,
I will need help to get this working.
Lets just think of it as me having two columns. one titled start and one end.

The only thing is (this adds a lot more complications - and possibly renders the problem unsolvable) the times are for a service team.
They work monday to friday, including bank holidays (so 5 days a week) and they work 12 hours a day (set quantity).
I need to somehow work out the total number of hours excluding weekends, if the date range happens to fall over one!

If this cant be done, I would still be interested in the 'non-weekend' sensitive version, as I dont really have a clue about coding in Excel!

thanks,
Matt

 

[pcdaveh]

First lets just get you up and running with the code the code the way it is now. Then we'll see about making modifications for weekends and holiday's. Go to key word tools on your menu bar and choose Macros and choose visual basic editor. Your now in the VBA Editor (Visual basic for Applications Editor. This is available in all the MS Office products for enhanced functionality. Go to the key word insert on the menu bar and choose module. Copy the code that is in explorer into the White screen area that appeared. Let me know when you've done that.

 

[matpj]

right.

done that. what now?!!

by the way, thanks for this, it is very kind of you!

Matt

 

[pcdaveh]

Close down the VBA Editor / or just go back to excel spread sheet. Type your dates and times into A1 and B1. Go to C1 and Key word Insert then go down to Function. Under the Function Category box scroll down until you come to User Defined. In the right hand box choose dhFormatInverval. With your mouse click on the cell that has your begining date and time which should be A1 and then move down in the function box to DateEnd and click on the cell that has your End date and time which should be B1 in the last box on the function put &quot;D HH:MM:SS&quot;. Let me know OK.

 

[JVFriederick]

Say A1=BeginTime, B2=EndTime

Days = INT(+B1-A1)
Hours = INT((+(B1-A1)-INT(B1-A1))*24)
Minutes = ((+(B1-A1)-INT(B1-A1))*24-INT((+(B1-A1)-INT(B1-A1))*24))*60

If you write the each as a separate equation in columns C, D, E you can combine them in F to get it spelled out :
=+TEXT(C1,0)&&quot; Days &quot;&TEXT(D1,0)&&quot; Hours &quot;&TEXT(E1,0)&&quot; Minutes&quot;

 

[svanels]

Another kiss approach

say B2 = start time, C2 = end time

The answers you can fill in a column and after that combine to full fill your needs

Days elapsed: =day(C2-B2)
Hours elapsed: =hour(C2-B2)
Minutes elapsed =minute(C2-B2)

Remember to put the number format of your start & end time to: 3/4/97 1:30

Then I would use the construction of JVFriederick

=+TEXT(C1,0)&&quot; Days &quot;&TEXT(D1,0)&&quot; Hours &quot;&TEXT(E1,0)&&quot; Minutes&quot;

Regards S. van Els
SAvanEls@cq-link.sr

 

[JVFriederick]

I think we've lost matpj.

 

[matpj]

No not lost. I forgot to tick 'email notification', so I thought I had been forgotten!
plus I'm not sure if you are in the USA but, I'm in UK and I finished yesterday, not long after I did the forst part of pasting the code!

ANYWAY....

I did this, and it works like a charm.

now. the next obstacle would be for it to recognise weekends and remove them from the dime elapsed.

also, as the days are 12 hours of work (7am-7pm) in this case, a way of discovering the exact period of time that was worked!

have I taxed your brains enough?!!


why cant I tick the email notificatino field?!!!

 

[matpj]

Does anyone have any ideas about this, or is it too nasty to even begin to apprach!

Matt