I'm trying to write a VBA macro to take the difference between two times
input by the user, calculate the difference, and put that value in the cell.

The first input box prompts for the start time (i.e. 9:00AM) and the second
prompts for the end time (i.e. 5:30PM). I've searched through this newsgroup
and done Google searches, but something just isn't sinking in. I need the
value to come out as a decimal value. With the 9:00 - 5:30 example, the cell
should show 8.5, but I keep getting -0.15. No doubt I'm missing something
obvious!

Thanks,
John

Post up the code so we can see what you are doing.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"John Schneider" wrote in message
...
I'm trying to write a VBA macro to take the difference between two times
input by the user, calculate the difference, and put that value in the
cell.

The first input box prompts for the start time (i.e. 9:00AM) and the
second
prompts for the end time (i.e. 5:30PM). I've searched through this
newsgroup
and done Google searches, but something just isn't sinking in. I need the
value to come out as a decimal value. With the 9:00 - 5:30 example, the
cell
should show 8.5, but I keep getting -0.15. No doubt I'm missing something
obvious!

Thanks,
John

Its definately a work in progress, but here's what I have so far:

Sub testjs()

Dim startTime As String
Dim endTime As String
Dim totHours As String

startTime = Application.InputBox("Enter Start Time", "Calculate Hours", , ,
, , , 1)
endTime = Application.InputBox("Enter End Time", "Calculate Hours", , , , ,
, 1)

totHours = CDate(endTime) - CDate(startTime)

ActiveCell.Value = totHours

End Sub



"Bob Phillips" wrote:

Post up the code so we can see what you are doing.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"John Schneider" wrote in message
...
I'm trying to write a VBA macro to take the difference between two times
input by the user, calculate the difference, and put that value in the
cell.

The first input box prompts for the start time (i.e. 9:00AM) and the
second
prompts for the end time (i.e. 5:30PM). I've searched through this
newsgroup
and done Google searches, but something just isn't sinking in. I need the
value to come out as a decimal value. With the 9:00 - 5:30 example, the
cell
should show 8.5, but I keep getting -0.15. No doubt I'm missing something
obvious!

Thanks,
John

Sub testjs()

Dim startTime As Date
Dim endTime As Date
Dim totHours As Date

startTime = Application.InputBox("Enter Start Time", "Calculate Hours", , ,
, , , 1) * 24
endTime = Application.InputBox("Enter End Time", "Calculate Hours", , , , ,
, 1) * 24

totHours = endTime - startTime
ActiveCell.Value = totHours
ActiveCell.NumberFormat = "00.00"
End Sub

StartTime entered as 9:0
EndTime entered as 17:30

Is this OK?


"John Schneider" wrote:

Its definately a work in progress, but here's what I have so far:

Sub testjs()

Dim startTime As String
Dim endTime As String
Dim totHours As String

startTime = Application.InputBox("Enter Start Time", "Calculate Hours", , ,
, , , 1)
endTime = Application.InputBox("Enter End Time", "Calculate Hours", , , , ,
, 1)

totHours = CDate(endTime) - CDate(startTime)

ActiveCell.Value = totHours

End Sub



"Bob Phillips" wrote:

Post up the code so we can see what you are doing.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"John Schneider" wrote in message
...
I'm trying to write a VBA macro to take the difference between two times
input by the user, calculate the difference, and put that value in the
cell.

The first input box prompts for the start time (i.e. 9:00AM) and the
second
prompts for the end time (i.e. 5:30PM). I've searched through this
newsgroup
and done Google searches, but something just isn't sinking in. I need the
value to come out as a decimal value. With the 9:00 - 5:30 example, the
cell
should show 8.5, but I keep getting -0.15. No doubt I'm missing something
obvious!

Thanks,
John

Billy,

That works perfect, but I don't understand why the time input (i.e. 9:00)
has to be multiplied by 24.

Thanks again!!!

"Billy Liddel" wrote:

Sub testjs()

Dim startTime As Date
Dim endTime As Date
Dim totHours As Date

startTime = Application.InputBox("Enter Start Time", "Calculate Hours", , ,
, , , 1) * 24
endTime = Application.InputBox("Enter End Time", "Calculate Hours", , , , ,
, 1) * 24

totHours = endTime - startTime
ActiveCell.Value = totHours
ActiveCell.NumberFormat = "00.00"
End Sub

StartTime entered as 9:0
EndTime entered as 17:30

Is this OK?


"John Schneider" wrote:

Its definately a work in progress, but here's what I have so far:

Sub testjs()

Dim startTime As String
Dim endTime As String
Dim totHours As String

startTime = Application.InputBox("Enter Start Time", "Calculate Hours", , ,
, , , 1)
endTime = Application.InputBox("Enter End Time", "Calculate Hours", , , , ,
, 1)

totHours = CDate(endTime) - CDate(startTime)

ActiveCell.Value = totHours

End Sub



"Bob Phillips" wrote:

Post up the code so we can see what you are doing.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"John Schneider" wrote in message
...
I'm trying to write a VBA macro to take the difference between two times
input by the user, calculate the difference, and put that value in the
cell.

The first input box prompts for the start time (i.e. 9:00AM) and the
second
prompts for the end time (i.e. 5:30PM). I've searched through this
newsgroup
and done Google searches, but something just isn't sinking in. I need the
value to come out as a decimal value. With the 9:00 - 5:30 example, the
cell
should show 8.5, but I keep getting -0.15. No doubt I'm missing something
obvious!

Thanks,
John

John

Excel calculates dates as numbers, normally counting from 1/1/1900. Time is
calculated as fractions of a day. so 9:00 is 9/24 of a day. Bear this in mind
when making any calculations with time.

Peter

"John Schneider" wrote:

Billy,

That works perfect, but I don't understand why the time input (i.e. 9:00)
has to be multiplied by 24.

Thanks again!!!

"Billy Liddel" wrote:

Sub testjs()

Dim startTime As Date
Dim endTime As Date
Dim totHours As Date

startTime = Application.InputBox("Enter Start Time", "Calculate Hours", , ,
, , , 1) * 24
endTime = Application.InputBox("Enter End Time", "Calculate Hours", , , , ,
, 1) * 24

totHours = endTime - startTime
ActiveCell.Value = totHours
ActiveCell.NumberFormat = "00.00"
End Sub

StartTime entered as 9:0
EndTime entered as 17:30

Is this OK?


"John Schneider" wrote:

Its definately a work in progress, but here's what I have so far:

Sub testjs()

Dim startTime As String
Dim endTime As String
Dim totHours As String

startTime = Application.InputBox("Enter Start Time", "Calculate Hours", , ,
, , , 1)
endTime = Application.InputBox("Enter End Time", "Calculate Hours", , , , ,
, 1)

totHours = CDate(endTime) - CDate(startTime)

ActiveCell.Value = totHours

End Sub



"Bob Phillips" wrote:

Post up the code so we can see what you are doing.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"John Schneider" wrote in message
...
I'm trying to write a VBA macro to take the difference between two times
input by the user, calculate the difference, and put that value in the
cell.

The first input box prompts for the start time (i.e. 9:00AM) and the
second
prompts for the end time (i.e. 5:30PM). I've searched through this
newsgroup
and done Google searches, but something just isn't sinking in. I need the
value to come out as a decimal value. With the 9:00 - 5:30 example, the
cell
should show 8.5, but I keep getting -0.15. No doubt I'm missing something
obvious!

Thanks,
John

Great explanation, and thanks again for the help!!!!

"Billy Liddel" wrote:

John

Excel calculates dates as numbers, normally counting from 1/1/1900. Time is
calculated as fractions of a day. so 9:00 is 9/24 of a day. Bear this in mind
when making any calculations with time.

Peter

"John Schneider" wrote:

Billy,

That works perfect, but I don't understand why the time input (i.e. 9:00)
has to be multiplied by 24.

Thanks again!!!

"Billy Liddel" wrote:

Sub testjs()

Dim startTime As Date
Dim endTime As Date
Dim totHours As Date

startTime = Application.InputBox("Enter Start Time", "Calculate Hours", , ,
, , , 1) * 24
endTime = Application.InputBox("Enter End Time", "Calculate Hours", , , , ,
, 1) * 24

totHours = endTime - startTime
ActiveCell.Value = totHours
ActiveCell.NumberFormat = "00.00"
End Sub

StartTime entered as 9:0
EndTime entered as 17:30

Is this OK?


"John Schneider" wrote:

Its definately a work in progress, but here's what I have so far:

Sub testjs()

Dim startTime As String
Dim endTime As String
Dim totHours As String

startTime = Application.InputBox("Enter Start Time", "Calculate Hours", , ,
, , , 1)
endTime = Application.InputBox("Enter End Time", "Calculate Hours", , , , ,
, 1)

totHours = CDate(endTime) - CDate(startTime)

ActiveCell.Value = totHours

End Sub



"Bob Phillips" wrote:

Post up the code so we can see what you are doing.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"John Schneider" wrote in message
...
I'm trying to write a VBA macro to take the difference between two times
input by the user, calculate the difference, and put that value in the
cell.

The first input box prompts for the start time (i.e. 9:00AM) and the
second
prompts for the end time (i.e. 5:30PM). I've searched through this
newsgroup
and done Google searches, but something just isn't sinking in. I need the
value to come out as a decimal value. With the 9:00 - 5:30 example, the
cell
should show 8.5, but I keep getting -0.15. No doubt I'm missing something
obvious!

Thanks,
John

One more thing I can't figure out. The user inputs a start time, end time,
and how long for lunch. Something is wrong with the way I'm trying to
calculate this. The start and end times are absolute times (i.e. 8:30 am and
5:30 pm), but I need the "how long for lunch" to be just the number of
minutes, i.e. 45, 60, etc. How do I get this to work?

Here's my code:

Dim startTime As Date
Dim endTime As Date
Dim Lunch As Date
Dim totHours As Date

startTime = Application.InputBox("Enter Start Time", "Calculate Hours", , ,
, , , 1)
endTime = Application.InputBox("Enter End Time", "Calculate Hours", , , , ,
, 1)
Lunch = Application.InputBox("How Long For Lunch?", "Calculate Hours", , , ,
, , 1)

totHours = ((endTime - startTime) * 24) - (Lunch * 24)

ActiveCell.Value = totHours
ActiveCell.NumberFormat = "#0.0"


"Billy Liddel" wrote:

John

Excel calculates dates as numbers, normally counting from 1/1/1900. Time is
calculated as fractions of a day. so 9:00 is 9/24 of a day. Bear this in mind
when making any calculations with time.

Peter

Divide the number of minutes by 1440 (24*60) to get a time serial
equivalent to the number of minutes.

Cliff Edwards

I was browsing through here and saw this thread... It's from earlier this year, but on the off chance that you'll find this helpful, here are some functions that demonstrate working with date/time variables and calculating the difference between two date/times.

(hopefully the post will be rendered legibly by the system)

'================================================= =============================
' Function TimeDifferenceString(ByVal dateStart as Date, ByVal dateEnd as Date,
' Optional ByVal blPad As Boolean = True,
' Optional ByVal intMinPlace As Integer = 0) As String
'
'------------------------------------------------------------------------------
' Given two Date variables, return a string that holds the difference between
' the date/times in days, hours, minutes, and seconds, formatted as
' "#d-#h:#m:#s".
'
'------------------------------------------------------------------------------
' Arguments:
'
' dateStart The first (or start) date/time.
'
' dateEnd The first (or end) date/time.
'
' blPad True: Pad each # (day, hour, min, sec) to a minimum of
' two digits, with a leading 0.
'
' False: Do not pad #s. 0 will still be returned as "0".
'
' intMinPlace What place (if any) to build the string out to even
' if that place (and all higher places) have a value of 0.
'
' <0 : do not automatically include any places; a time
' difference of 0 will result in an empty return string
'
' 0 : always include seconds; a time difference of 0 will
' result in a return string of "0s" (or "00s", if
' blPad is True)
'
' 1 : always include minutes: a time difference of 0
' minutes will result in a return string of "0m:#s"
' (or "00m:##s", if blPad is True)
'
' 2 : always include hours: a time difference of 0 hours
' will result in a return string of "0h:#m:#s" (or
' "00h:##m:##s", if blPad is True)
'
' =3 : always include days: a time difference of 0 days
' will result in a return string of "0d-#h:#m:#s" (or
' "0d-##h:##m:##s", if blPad is True)
'
'------------------------------------------------------------------------------
' Return value: A string in the format of "#d-#h:#m:#s". See blPad and
' intMinPlace arguments for details on controlling the format of the output.
'
'------------------------------------------------------------------------------
' Greg Scheidel, , 2008.12.27
'------------------------------------------------------------------------------
'
Function TimeDifferenceString(ByVal dateStart As Date, ByVal dateEnd As Date, _
Optional ByVal blPad As Boolean = True, _
Optional ByVal intMinPlace As Integer = 0) As String

' Variables:
'
' blCarry For each time calculation, whether a number was carried
' over from the next lower place.
'
' intDay Calculated # of days difference between dateStart and
' dateEnd.
'
' intHour Calculated # of hours difference between dateStart and
' dateEnd.
'
' intMinute Calculated # of minutes difference between dateStart and
' dateEnd.
'
' intSecond Calculated # of seconds difference between dateStart and
'
' intTemp Temp integer used in time calculations.
'
' strFormat Calculated format for time #s in return string; based on
' blPad argument.
' dateEnd.

Dim intTemp As Integer
Dim strFormat As String

Dim intSecond As Integer
Dim intMinute As Integer
Dim intHour As Integer
Dim intDay As Integer

Dim blCarry As Boolean

' set a format string for the Format() function, based on whether blPad is
' True.
'
' if blPad is True, then we will format all #s with a minimum of two
' places. if blPad is False, then we will format all #s with a minimum of
' one place (i.e., just the bare number).
strFormat = String(IIf(blPad, 2, 1), "0")

' General calculation info...
'
' Date/times are stored in Date variables as IEEE 64-bit (8-byte)
' floating-point numbers that represent dates ranging from 1 January 1900
' to 31 December 9999 and times from 0:00:00 to 23:59:59 [MS VB Help, topic
' "Date Data Type"].
'
' Each integer unit represents one day. So we can calculate days, hours,
' minutes, and seconds since 1900.01.01 as:
'
' Days = Date
'
' Hours = Date * 24
'
' Minutes = Date * 24 * 60
'
' Seconds = Date * 24 * 60 * 60
'
' Since Date varaiables are just numbers, we can also subtract or add them
' to calculate totals and differences. So given dateStart and dateEnd, the
' time difference between them can be calculated as:
'
' Days = (dateEnd - dateStart)
'
' Hours = (dateEnd - dateStart) * 24
'
' Minutes = (dateEnd - dateStart) * 24 * 60
'
' Seconds = (dateEnd - dateStart) * 24 * 60 * 60
'
' However, when a Date variable is displayed as a date the number is
' rounded to the nearest second. So we need to take this into account if
' we want our results to be consistent with the rounding of the original
' start and end dates.
'
' In the case of seconds, we can round but need to note whether we are
' rounding from 59 seconds to 60 seconds, because that 60 seconds gets
' displayed as 0 seconds... with an extra minute carried over and added to
' the minutes calculation.
'
' In the case of minutes, we take the integer portion of the caculation but
' have to add the extra minute (if any) carried over from the seconds
' calculation. We then have to note whether this has resulted in rounding
' up from 59 minutes to 60 minutes, because that 60 minutes gets displayed
' as 0 minutes... with an extra hour carried over and added to the hours
' calculation.
'
' In the case of hours, we take the integer portion of the caculation but
' have to add the extra hour (if any) carried over from the minutes
' calculation. We then have to note whether this has resulted in rounding
' up from 23 hours to 24 hours, because that 24 hours gets displayed as 0
' hours... with an extra day carried over and added to the day calculation.
'
' In the case of days, we take the integer portion of the caculation but
' have to add the extra day (if any) carried over from the hours
' calculation.

' ----- calculate seconds -----
intSecond = Round((dateEnd - dateStart) * 24 * 60 * 60) Mod 60
intTemp = Int((dateEnd - dateStart) * 24 * 60 * 60) Mod 60

' if Round() and Int() yielded different results, and Round() yielded a 0,
' then we rounded from 59.[5-9] to 0; carry 1 minute (to add to minutes)
blCarry = ((intSecond < intTemp) And (intSecond = 0))

' ----- calculate minutes -----
intMinute = (Int((dateEnd - dateStart) * 24 * 60) + IIf(blCarry, 1, 0)) _
Mod 60

' if intMinute is 0, and we carried 1 minute from the seconds calculation,
' then we rounded from 59.[5-9] to 0; carry 1 hour (to add to hours)
blCarry = ((intMinute = 0) And blCarry)

' ----- calculate hours -----
intHour = (Int((dateEnd - dateStart) * 24) + IIf(blCarry, 1, 0)) Mod 24

' if intHour is 0, and we carried 1 hour from the minutes calculation, then
' we rounded from 23.[5-9] to 0; carry 1 day (to add to days)
blCarry = ((intHour = 0) And blCarry)

' ----- calculate days -----
intDay = Int(dateEnd - dateStart) + IIf(blCarry, 1, 0)

' ----- build return string -----

' now that we have the values, build the return string, starting with days
' and working down to seconds. this allows us to include only (a) non-zero
' places, and (b) zero places that are a lower place than a non-zero place.
' see the intMinPlace argument for more details.
'
' if we didn't need this functionality (the ability to keep the return
' string to a minimum size) then we could just build the string as we
' calculated the numbers.

' include days place if value is non-zero or intMinPlace specifies days
If ((intDay < 0) Or (intMinPlace = 3)) Then
TimeDifferenceString = Format(intDay, strFormat) & "d-"
End If

' include hours place if value is non-zero or intMinPlace specifies hours
If ((intHour < 0) Or (intMinPlace = 2)) Then
TimeDifferenceString = TimeDifferenceString & _
Format(intHour, strFormat) & "h:"
End If

' include minutes place if value is non-zero or intMinPlace specifies
' minutes
If ((intMinute < 0) Or (intMinPlace = 1)) Then
TimeDifferenceString = TimeDifferenceString & _
Format(intMinute, strFormat) & "m:"
End If

' include seconds place if value is non-zero or intMinPlace specifies
' seconds
If ((intSecond < 0) Or (intMinPlace = 0)) Then
TimeDifferenceString = TimeDifferenceString & _
Format(intSecond, strFormat) & "s"
End If

End Function
'================================================= =============================
' Function TimeDifferenceArray(ByVal dateStart as Date, ByVal dateEnd as Date)
' As Integer()
'
'------------------------------------------------------------------------------
' Given two Date variables, return an array that holds the difference between
' the date/times in days, hours, minutes, and seconds.
'
'------------------------------------------------------------------------------
' Arguments:
'
' dateStart The first (or start) date/time.
'
' dateEnd The first (or end) date/time.
'
'------------------------------------------------------------------------------
' Return value: A one-dimensional array whe
'
' array(0) : seconds difference
' array(1) : minutes difference
' array(2) : hours difference
' array(3) : days difference
'
'------------------------------------------------------------------------------
' Greg Scheidel, , 2008.12.27
'------------------------------------------------------------------------------
'
Function TimeDifferenceArray(ByVal dateStart As Date, ByVal dateEnd As Date) _
As Integer()

' Variables:
'
' blCarry For each time calculation, whether a number was carried
' over from the next lower place.
'
' intRetArray Array used to hold the calculation results before exiting
' function.
'
' (0) Calculated # of seconds difference between dateStart and
'
' (1) Calculated # of minutes difference between dateStart and
' dateEnd.
'
' (2) Calculated # of hours difference between dateStart and
' dateEnd.
'
' (3) Calculated # of days difference between dateStart and
' dateEnd.
'
' intTemp Temp integer used in time calculations.
'

Dim intTemp As Integer
Dim blCarry As Boolean

Dim intRetArray(0 To 3) As Integer

' General calculation info...
'
' Date/times are stored in Date variables as IEEE 64-bit (8-byte)
' floating-point numbers that represent dates ranging from 1 January 1900
' to 31 December 9999 and times from 0:00:00 to 23:59:59 [MS VB Help, topic
' "Date Data Type"].
'
' Each integer unit represents one day. So we can calculate days, hours,
' minutes, and seconds since 1900.01.01 as:
'
' Days = Date
'
' Hours = Date * 24
'
' Minutes = Date * 24 * 60
'
' Seconds = Date * 24 * 60 * 60
'
' Since Date varaiables are just numbers, we can also subtract or add them
' to calculate totals and differences. So given dateStart and dateEnd, the
' time difference between them can be calculated as:
'
' Days = (dateEnd - dateStart)
'
' Hours = (dateEnd - dateStart) * 24
'
' Minutes = (dateEnd - dateStart) * 24 * 60
'
' Seconds = (dateEnd - dateStart) * 24 * 60 * 60
'
' However, when a Date variable is displayed as a date the number is
' rounded to the nearest second. So we need to take this into account if
' we want our results to be consistent with the rounding of the original
' start and end dates.
'
' In the case of seconds, we can round but need to note whether we are
' rounding from 59 seconds to 60 seconds, because that 60 seconds gets
' displayed as 0 seconds... with an extra minute carried over and added to
' the minutes calculation.
'
' In the case of minutes, we take the integer portion of the caculation but
' have to add the extra minute (if any) carried over from the seconds
' calculation. We then have to note whether this has resulted in rounding
' up from 59 minutes to 60 minutes, because that 60 minutes gets displayed
' as 0 minutes... with an extra hour carried over and added to the hours
' calculation.
'
' In the case of hours, we take the integer portion of the caculation but
' have to add the extra hour (if any) carried over from the minutes
' calculation. We then have to note whether this has resulted in rounding
' up from 23 hours to 24 hours, because that 24 hours gets displayed as 0
' hours... with an extra day carried over and added to the day calculation.
'
' In the case of days, we take the integer portion of the caculation but
' have to add the extra day (if any) carried over from the hours
' calculation.

' ----- calculate seconds -----
intRetArray(0) = Round((dateEnd - dateStart) * 24 * 60 * 60) Mod 60
intTemp = Int((dateEnd - dateStart) * 24 * 60 * 60) Mod 60

' if Round() and Int() yielded different results, and Round() yielded a 0,
' then we rounded from 59.[5-9] to 0; carry 1 minute (to add to minutes)
blCarry = ((intRetArray(0) < intTemp) And (intRetArray(0) = 0))

' ----- calculate minutes -----
intRetArray(1) = _
(Int((dateEnd - dateStart) * 24 * 60) + IIf(blCarry, 1, 0)) Mod 60

' if intRetArray(1) is 0, and we carried 1 minute from the seconds
' calculation, then we rounded from 59.[5-9] to 0; carry 1 hour (to add to
' hours)
blCarry = ((intRetArray(1) = 0) And blCarry)

' ----- calculate hours -----
intRetArray(2) = _
(Int((dateEnd - dateStart) * 24) + IIf(blCarry, 1, 0)) Mod 24

' if intRetArray(2) is 0, and we carried 1 hour from the minutes
' calculation, then we rounded from 23.[5-9] to 0; carry 1 day (to add to
' days)
blCarry = ((intRetArray(2) = 0) And blCarry)

' ----- calculate days -----
intRetArray(3) = Int(dateEnd - dateStart) + IIf(blCarry, 1, 0)

TimeDifferenceArray = intRetArray

End Function