I have a fix already, but would like to use what I've created here
 

I know why I'm getting the error, but I would like to know how to fix it.

The spreadsheet cells contain the following:

A1: StartDate
A2: 12/12/08
B1: EndDate
B2: 12/15/08
F1: StartTime (Unplanned)
F2: 12:20
G1: EndTime (Unplanned)
G2: 14:30
I1: TotalDowntime
I2: This cell is blank and where the end result of Main() will go.

________________________________
Sub Main()

Range("A1").Select

Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=x1Next, _
MatchCase:=False).Activate

ActiveCell.Offset(-1, 8).Activate

startdate = ActiveCell.Offset(0, -8).Value
enddate = ActiveCell.Offset(0, -7).Value

daysdown = DateDiff("D", startdate, enddate)

starttime = ActiveCell.Offset(0, -3).Value
endtime = ActiveCell.Offset(0, -2).Value

hourdiff = starttime - endtime

daysvshours = daysdown * 24 & ":00:00"

totalhours = daysvshours - hourdiff <-------------Gives the error "Type
Mismatch (Error 13)"

ActiveCell.Value = totalhours

End Sub


__________________

I have a fix already, but would like to use what I've created here
 

Comment out the formatting of the daysvshours variable live this only:
daysvshours = daysdown * 24

--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Optatus" wrote:

I know why I'm getting the error, but I would like to know how to fix it.

The spreadsheet cells contain the following:

A1: StartDate
A2: 12/12/08
B1: EndDate
B2: 12/15/08
F1: StartTime (Unplanned)
F2: 12:20
G1: EndTime (Unplanned)
G2: 14:30
I1: TotalDowntime
I2: This cell is blank and where the end result of Main() will go.

________________________________
Sub Main()

Range("A1").Select

Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=x1Next, _
MatchCase:=False).Activate

ActiveCell.Offset(-1, 8).Activate

startdate = ActiveCell.Offset(0, -8).Value
enddate = ActiveCell.Offset(0, -7).Value

daysdown = DateDiff("D", startdate, enddate)

starttime = ActiveCell.Offset(0, -3).Value
endtime = ActiveCell.Offset(0, -2).Value

hourdiff = starttime - endtime

daysvshours = daysdown * 24 & ":00:00"

totalhours = daysvshours - hourdiff <-------------Gives the error "Type
Mismatch (Error 13)"

ActiveCell.Value = totalhours

End Sub


__________________

I have a fix already, but would like to use what I've created
 

Well when I do that I don't exactly get what I'm looking for the outcome.

Here's what I get 1730:10:00
Here's what it should be:

These are returned if they are set to the ActiveCell.Value at the end of the
mod...

daysvsHours should return: 72:00:00
hourdiff should return: -0.0902777777777777

This should be the endresult of the original mod without any modifications....

totalhours should be 74:10:00

I know that it doesn't like the string in daysvshours... is there a way to
fix that without populating two cells and subtracting two different cells
(which is the work around that works).

"Michael" wrote:

Comment out the formatting of the daysvshours variable live this only:
daysvshours = daysdown * 24

--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Optatus" wrote:

I know why I'm getting the error, but I would like to know how to fix it.

The spreadsheet cells contain the following:

A1: StartDate
A2: 12/12/08
B1: EndDate
B2: 12/15/08
F1: StartTime (Unplanned)
F2: 12:20
G1: EndTime (Unplanned)
G2: 14:30
I1: TotalDowntime
I2: This cell is blank and where the end result of Main() will go.

________________________________
Sub Main()

Range("A1").Select

Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=x1Next, _
MatchCase:=False).Activate

ActiveCell.Offset(-1, 8).Activate

startdate = ActiveCell.Offset(0, -8).Value
enddate = ActiveCell.Offset(0, -7).Value

daysdown = DateDiff("D", startdate, enddate)

starttime = ActiveCell.Offset(0, -3).Value
endtime = ActiveCell.Offset(0, -2).Value

hourdiff = starttime - endtime

daysvshours = daysdown * 24 & ":00:00"

totalhours = daysvshours - hourdiff <-------------Gives the error "Type
Mismatch (Error 13)"

ActiveCell.Value = totalhours

End Sub


__________________

I have a fix already, but would like to use what I've created
 

I get 71.48611111 on I2

--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Optatus" wrote:

Well when I do that I don't exactly get what I'm looking for the outcome.

Here's what I get 1730:10:00
Here's what it should be:

These are returned if they are set to the ActiveCell.Value at the end of the
mod...

daysvsHours should return: 72:00:00
hourdiff should return: -0.0902777777777777

This should be the endresult of the original mod without any modifications....

totalhours should be 74:10:00

I know that it doesn't like the string in daysvshours... is there a way to
fix that without populating two cells and subtracting two different cells
(which is the work around that works).

"Michael" wrote:

Comment out the formatting of the daysvshours variable live this only:
daysvshours = daysdown * 24

--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Optatus" wrote:

I know why I'm getting the error, but I would like to know how to fix it.

The spreadsheet cells contain the following:

A1: StartDate
A2: 12/12/08
B1: EndDate
B2: 12/15/08
F1: StartTime (Unplanned)
F2: 12:20
G1: EndTime (Unplanned)
G2: 14:30
I1: TotalDowntime
I2: This cell is blank and where the end result of Main() will go.

________________________________
Sub Main()

Range("A1").Select

Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=x1Next, _
MatchCase:=False).Activate

ActiveCell.Offset(-1, 8).Activate

startdate = ActiveCell.Offset(0, -8).Value
enddate = ActiveCell.Offset(0, -7).Value

daysdown = DateDiff("D", startdate, enddate)

starttime = ActiveCell.Offset(0, -3).Value
endtime = ActiveCell.Offset(0, -2).Value

hourdiff = starttime - endtime

daysvshours = daysdown * 24 & ":00:00"

totalhours = daysvshours - hourdiff <-------------Gives the error "Type
Mismatch (Error 13)"

ActiveCell.Value = totalhours

End Sub


__________________

I have a fix already, but would like to use what I've created
 

My apologie, I get 72.09027778 i did not have the end time

If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Optatus" wrote:

Well when I do that I don't exactly get what I'm looking for the outcome.

Here's what I get 1730:10:00
Here's what it should be:

These are returned if they are set to the ActiveCell.Value at the end of the
mod...

daysvsHours should return: 72:00:00
hourdiff should return: -0.0902777777777777

This should be the endresult of the original mod without any modifications....

totalhours should be 74:10:00

I know that it doesn't like the string in daysvshours... is there a way to
fix that without populating two cells and subtracting two different cells
(which is the work around that works).

"Michael" wrote:

Comment out the formatting of the daysvshours variable live this only:
daysvshours = daysdown * 24

--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Optatus" wrote:

I know why I'm getting the error, but I would like to know how to fix it.

The spreadsheet cells contain the following:

A1: StartDate
A2: 12/12/08
B1: EndDate
B2: 12/15/08
F1: StartTime (Unplanned)
F2: 12:20
G1: EndTime (Unplanned)
G2: 14:30
I1: TotalDowntime
I2: This cell is blank and where the end result of Main() will go.

________________________________
Sub Main()

Range("A1").Select

Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=x1Next, _
MatchCase:=False).Activate

ActiveCell.Offset(-1, 8).Activate

startdate = ActiveCell.Offset(0, -8).Value
enddate = ActiveCell.Offset(0, -7).Value

daysdown = DateDiff("D", startdate, enddate)

starttime = ActiveCell.Offset(0, -3).Value
endtime = ActiveCell.Offset(0, -2).Value

hourdiff = starttime - endtime

daysvshours = daysdown * 24 & ":00:00"

totalhours = daysvshours - hourdiff <-------------Gives the error "Type
Mismatch (Error 13)"

ActiveCell.Value = totalhours

End Sub


__________________

I have a fix already, but would like to use what I've created
 

If you get 1730:10:00 is because your Cell I2 is formatted as time -
37:30:55; which means you should reformat your cell to General
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Michael" wrote:

My apologie, I get 72.09027778 i did not have the end time

If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Optatus" wrote:

Well when I do that I don't exactly get what I'm looking for the outcome.

Here's what I get 1730:10:00
Here's what it should be:

These are returned if they are set to the ActiveCell.Value at the end of the
mod...

daysvsHours should return: 72:00:00
hourdiff should return: -0.0902777777777777

This should be the endresult of the original mod without any modifications....

totalhours should be 74:10:00

I know that it doesn't like the string in daysvshours... is there a way to
fix that without populating two cells and subtracting two different cells
(which is the work around that works).

"Michael" wrote:

Comment out the formatting of the daysvshours variable live this only:
daysvshours = daysdown * 24

--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Optatus" wrote:

I know why I'm getting the error, but I would like to know how to fix it.

The spreadsheet cells contain the following:

A1: StartDate
A2: 12/12/08
B1: EndDate
B2: 12/15/08
F1: StartTime (Unplanned)
F2: 12:20
G1: EndTime (Unplanned)
G2: 14:30
I1: TotalDowntime
I2: This cell is blank and where the end result of Main() will go.

________________________________
Sub Main()

Range("A1").Select

Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=x1Next, _
MatchCase:=False).Activate

ActiveCell.Offset(-1, 8).Activate

startdate = ActiveCell.Offset(0, -8).Value
enddate = ActiveCell.Offset(0, -7).Value

daysdown = DateDiff("D", startdate, enddate)

starttime = ActiveCell.Offset(0, -3).Value
endtime = ActiveCell.Offset(0, -2).Value

hourdiff = starttime - endtime

daysvshours = daysdown * 24 & ":00:00"

totalhours = daysvshours - hourdiff <-------------Gives the error "Type
Mismatch (Error 13)"

ActiveCell.Value = totalhours

End Sub


__________________