Hi all,

Is there a way to write 10 a´clock PM as 2200 in cell B1 and 7 a´clock AM as
0700 in cell C1, and get the correct value 9 hours in cell D1?

And/Or.....

color 9 cells, (just as the number of hours), and get the time value 2200
and 0700.

I hope there is........

Thanks for any help

//Thomas

Hi
try for the first one
=(C1<B1)+C1-B1

--
Regards
Frank Kabel
Frankfurt, Germany

Jonsson wrote:
Hi all,

Is there a way to write 10 a´clock PM as 2200 in cell B1 and 7
a´clock AM as 0700 in cell C1, and get the correct value 9 hours in
cell D1?

And/Or.....

color 9 cells, (just as the number of hours), and get the time value
2200 and 0700.

I hope there is........

Thanks for any help

//Thomas

Hi Frank
Thanks for your help!!
But I get the value -1499?! It´s probably because I have the wrong format of
the cell D1.
Can you please tell me what format it should be?

I suppose you don´t think my second whish was possible?

//Thomas
"Frank Kabel" skrev i meddelandet
...
Hi
try for the first one
=(C1<B1)+C1-B1

--
Regards
Frank Kabel
Frankfurt, Germany

Jonsson wrote:
Hi all,

Is there a way to write 10 a´clock PM as 2200 in cell B1 and 7
a´clock AM as 0700 in cell C1, and get the correct value 9 hours in
cell D1?

And/Or.....

color 9 cells, (just as the number of hours), and get the time value
2200 and 0700.

I hope there is........

Thanks for any help

//Thomas

Hi
for the first question:
- format your cells with a time format (Format - Cells - Time) They
should look like '10:00 AM'

The second one: Yes it is possible but you'll need VBA. e.g.
- put the code found below in a module of your workbook
- if you want to color in the range A1:Z1 with a color (lets say red)
and count the number of colored cells use
=SUMPRODUCT(--(ColorIndex(A1:Z1)=3))

- to get a time value use the formula
=SUMPRODUCT(--(ColorIndex(A1:Z1)=3))/24
and format this target cell as time


------Function Colorindex - Repost from Bob Phillips

'---------------------------------------------------------------------
Function ColorIndex(rng As Range, _
Optional text As Boolean = False) As Variant
'---------------------------------------------------------------------
' Function: Returns the colorindex of the supplied range
' Synopsis:
' Author: Bob Phillips/Harlan Grove
'
'---------------------------------------------------------------------
Dim cell As Range, row As Range
Dim i As Long, j As Long
Dim iWhite As Long, iBlack As Long
Dim aryColours As Variant

If rng.Areas.Count 1 Then
ColorIndex = CVErr(xlErrValue)
Exit Function
End If

iWhite = WhiteColorindex(rng.Worksheet.Parent)
iBlack = BlackColorindex(rng.Worksheet.Parent)

If rng.Cells.Count = 1 Then
If text Then
aryColours = DecodeColorIndex(rng, True, iBlack)
Else
aryColours = DecodeColorIndex(rng, False, iWhite)
End If

Else
aryColours = rng.Value
i = 0

For Each row In rng.Rows
i = i + 1
j = 0

For Each cell In row.Cells
j = j + 1

If text Then
aryColours(i, j) = DecodeColorIndex(cell, True,
iBlack)
Else
aryColours(i, j) = DecodeColorIndex(cell, False,
iWhite)
End If

Next cell

Next row

End If

ColorIndex = aryColours

End Function

Private Function WhiteColorindex(oWB As Workbook)
Dim iPalette As Long
WhiteColorindex = 0
For iPalette = 1 To 56
If oWB.Colors(iPalette) = &HFFFFFF Then
WhiteColorindex = iPalette
Exit Function
End If
Next iPalette
End Function

Private Function BlackColorindex(oWB As Workbook)
Dim iPalette As Long
BlackColorindex = 0
For iPalette = 1 To 56
If oWB.Colors(iPalette) = &H0 Then
BlackColorindex = iPalette
Exit Function
End If
Next iPalette
End Function

Private Function DecodeColorIndex(rng As Range, text As Boolean, idx As
Long)
Dim iColor As Long
If text Then
iColor = rng.font.ColorIndex
Else
iColor = rng.Interior.ColorIndex
End If
If iColor < 0 Then
iColor = idx
End If
DecodeColorIndex = iColor
End Function





--
Regards
Frank Kabel
Frankfurt, Germany



Jonsson wrote:
Hi Frank
Thanks for your help!!
But I get the value -1499?! It´s probably because I have the wrong
format of the cell D1.
Can you please tell me what format it should be?

I suppose you don´t think my second whish was possible?

//Thomas
"Frank Kabel" skrev i meddelandet
...
Hi
try for the first one
=(C1<B1)+C1-B1

--
Regards
Frank Kabel
Frankfurt, Germany

Jonsson wrote:
Hi all,

Is there a way to write 10 a´clock PM as 2200 in cell B1 and 7
a´clock AM as 0700 in cell C1, and get the correct value 9 hours in
cell D1?

And/Or.....

color 9 cells, (just as the number of hours), and get the time
value
2200 and 0700.

I hope there is........

Thanks for any help

//Thomas

Hi Frank!

First...
Is it impossible to have the time as "2200"? It would be easier to write
than to write "22:00"......................?

Second.....
I assume I´m supposed to drive the code, but when I do, I get the question
"Makroname".

Third.........

When I paste the formula in AA1 I get "Name?"

Fourth.......

How to change the code so that I get a separate value
if I make one cell in another color ?

In short terms this is what I want to do:

color1 from B1 to O1 is 16 cells, these cells are representing 15 minutes
each, totally 4 hours.
That give me in B2"0800" and C2"1200"
B1 represent "0800" and O1 represent "1200".

so, depending of how many cells you colors you get a time value at lets say
Z1.
Also, there is a need of if I color any of these cells in Color2 the total
value of the cells Z1 should be decreased by that number of cells.

I really appreaciate your help and hope you can help me with this!
Thomas, Sweden

"Frank Kabel" skrev i meddelandet
...
Hi
for the first question:
- format your cells with a time format (Format - Cells - Time) They
should look like '10:00 AM'

The second one: Yes it is possible but you'll need VBA. e.g.
- put the code found below in a module of your workbook
- if you want to color in the range A1:Z1 with a color (lets say red)
and count the number of colored cells use
=SUMPRODUCT(--(ColorIndex(A1:Z1)=3))

- to get a time value use the formula
=SUMPRODUCT(--(ColorIndex(A1:Z1)=3))/24
and format this target cell as time


------Function Colorindex - Repost from Bob Phillips

'---------------------------------------------------------------------
Function ColorIndex(rng As Range, _
Optional text As Boolean = False) As Variant
'---------------------------------------------------------------------
' Function: Returns the colorindex of the supplied range
' Synopsis:
' Author: Bob Phillips/Harlan Grove
'
'---------------------------------------------------------------------
Dim cell As Range, row As Range
Dim i As Long, j As Long
Dim iWhite As Long, iBlack As Long
Dim aryColours As Variant

If rng.Areas.Count 1 Then
ColorIndex = CVErr(xlErrValue)
Exit Function
End If

iWhite = WhiteColorindex(rng.Worksheet.Parent)
iBlack = BlackColorindex(rng.Worksheet.Parent)

If rng.Cells.Count = 1 Then
If text Then
aryColours = DecodeColorIndex(rng, True, iBlack)
Else
aryColours = DecodeColorIndex(rng, False, iWhite)
End If

Else
aryColours = rng.Value
i = 0

For Each row In rng.Rows
i = i + 1
j = 0

For Each cell In row.Cells
j = j + 1

If text Then
aryColours(i, j) = DecodeColorIndex(cell, True,
iBlack)
Else
aryColours(i, j) = DecodeColorIndex(cell, False,
iWhite)
End If

Next cell

Next row

End If

ColorIndex = aryColours

End Function

Private Function WhiteColorindex(oWB As Workbook)
Dim iPalette As Long
WhiteColorindex = 0
For iPalette = 1 To 56
If oWB.Colors(iPalette) = &HFFFFFF Then
WhiteColorindex = iPalette
Exit Function
End If
Next iPalette
End Function

Private Function BlackColorindex(oWB As Workbook)
Dim iPalette As Long
BlackColorindex = 0
For iPalette = 1 To 56
If oWB.Colors(iPalette) = &H0 Then
BlackColorindex = iPalette
Exit Function
End If
Next iPalette
End Function

Private Function DecodeColorIndex(rng As Range, text As Boolean, idx As
Long)
Dim iColor As Long
If text Then
iColor = rng.font.ColorIndex
Else
iColor = rng.Interior.ColorIndex
End If
If iColor < 0 Then
iColor = idx
End If
DecodeColorIndex = iColor
End Function





--
Regards
Frank Kabel
Frankfurt, Germany



Jonsson wrote:
Hi Frank
Thanks for your help!!
But I get the value -1499?! It´s probably because I have the wrong
format of the cell D1.
Can you please tell me what format it should be?

I suppose you don´t think my second whish was possible?

//Thomas
"Frank Kabel" skrev i meddelandet
...
Hi
try for the first one
=(C1<B1)+C1-B1

--
Regards
Frank Kabel
Frankfurt, Germany

Jonsson wrote:
Hi all,

Is there a way to write 10 a´clock PM as 2200 in cell B1 and 7
a´clock AM as 0700 in cell C1, and get the correct value 9 hours in
cell D1?

And/Or.....

color 9 cells, (just as the number of hours), and get the time
value
2200 and 0700.

I hope there is........

Thanks for any help

//Thomas

Hi
see below - so many questions :-)

Jonsson wrote:
Hi Frank!

First...
Is it impossible to have the time as "2200"? It would be easier to
write than to write "22:00"......................?

You can write is this way but all calculation formulas would get quite
complex as you loose Excel's time support - don't do this. Add the ':'
Though it is possible to write a worksheet_change macro which will
convert these entries to a time format i would stick to the normal
entry
(but if you're interested have a look at
http://www.cpearson.com/excel/DateTimeEntry.htm for such code)


Second.....
I assume I´m supposed to drive the code, but when I do, I get the
question "Makroname".

One question up-front: Do you use the English Excel version (your word
'Makroname' suggests a non english version).
What do you mean with 'drive'?. Do the following:
- Open your workbook
- Hit ALT F11 to oben the VBA editor
- Create a new module (right click in the explorer tree and add a new
module)
- paste the code
- close the VBA editor.

Third.......
When I paste the formula in AA1 I get "Name?"

Should be solved by the above (for some more information how to use
macros have a look at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm)


Fourth.......
How to change the code so that I get a separate value
if I make one cell in another color ?

In short terms this is what I want to do:

color1 from B1 to O1 is 16 cells, these cells are representing 15
minutes each, totally 4 hours.
That give me in B2"0800" and C2"1200"
B1 represent "0800" and O1 represent "1200".
so, depending of how many cells you colors you get a time value at
lets say Z1.
Also, there is a need of if I color any of these cells in Color2 the
total value of the cells Z1 should be decreased by that number of
cells.

To get the time value in Z1 try
=(SUMPRODUCT(--(ColorIndex(B1:O1)=3))*15)/24
this will work for the color 'red' (red = 3) - it sums all red colored
cells. For more information abour color values have a look at
http://www.mvps.org/dmcritchie/excel/colors.htm



Frank


"Frank Kabel" skrev i meddelandet
...
Hi
for the first question:
- format your cells with a time format (Format - Cells - Time) They
should look like '10:00 AM'

The second one: Yes it is possible but you'll need VBA. e.g.
- put the code found below in a module of your workbook
- if you want to color in the range A1:Z1 with a color (lets say
red)
and count the number of colored cells use
=SUMPRODUCT(--(ColorIndex(A1:Z1)=3))

- to get a time value use the formula
=SUMPRODUCT(--(ColorIndex(A1:Z1)=3))/24
and format this target cell as time


------Function Colorindex - Repost from Bob Phillips


'---------------------------------------------------------------------
Function ColorIndex(rng As Range, _
Optional text As Boolean = False) As Variant

'---------------------------------------------------------------------
' Function: Returns the colorindex of the supplied range
' Synopsis:
' Author: Bob Phillips/Harlan Grove
'

'---------------------------------------------------------------------
Dim cell As Range, row As Range
Dim i As Long, j As Long
Dim iWhite As Long, iBlack As Long
Dim aryColours As Variant

If rng.Areas.Count 1 Then
ColorIndex = CVErr(xlErrValue)
Exit Function
End If

iWhite = WhiteColorindex(rng.Worksheet.Parent)
iBlack = BlackColorindex(rng.Worksheet.Parent)

If rng.Cells.Count = 1 Then
If text Then
aryColours = DecodeColorIndex(rng, True, iBlack)
Else
aryColours = DecodeColorIndex(rng, False, iWhite)
End If

Else
aryColours = rng.Value
i = 0

For Each row In rng.Rows
i = i + 1
j = 0

For Each cell In row.Cells
j = j + 1

If text Then
aryColours(i, j) = DecodeColorIndex(cell, True,
iBlack)
Else
aryColours(i, j) = DecodeColorIndex(cell, False,
iWhite)
End If

Next cell

Next row

End If

ColorIndex = aryColours

End Function

Private Function WhiteColorindex(oWB As Workbook)
Dim iPalette As Long
WhiteColorindex = 0
For iPalette = 1 To 56
If oWB.Colors(iPalette) = &HFFFFFF Then
WhiteColorindex = iPalette
Exit Function
End If
Next iPalette
End Function

Private Function BlackColorindex(oWB As Workbook)
Dim iPalette As Long
BlackColorindex = 0
For iPalette = 1 To 56
If oWB.Colors(iPalette) = &H0 Then
BlackColorindex = iPalette
Exit Function
End If
Next iPalette
End Function

Private Function DecodeColorIndex(rng As Range, text As Boolean, idx
As Long)
Dim iColor As Long
If text Then
iColor = rng.font.ColorIndex
Else
iColor = rng.Interior.ColorIndex
End If
If iColor < 0 Then
iColor = idx
End If
DecodeColorIndex = iColor
End Function





--
Regards
Frank Kabel
Frankfurt, Germany



Jonsson wrote:
Hi Frank
Thanks for your help!!
But I get the value -1499?! It´s probably because I have the wrong
format of the cell D1.
Can you please tell me what format it should be?

I suppose you don´t think my second whish was possible?

//Thomas
"Frank Kabel" skrev i meddelandet
...
Hi
try for the first one
=(C1<B1)+C1-B1

--
Regards
Frank Kabel
Frankfurt, Germany

Jonsson wrote:
Hi all,

Is there a way to write 10 a´clock PM as 2200 in cell B1 and 7
a´clock AM as 0700 in cell C1, and get the correct value 9 hours
in cell D1?

And/Or.....

color 9 cells, (just as the number of hours), and get the time
value
2200 and 0700.

I hope there is........

Thanks for any help

//Thomas