Concatenate>>Format>>Math

[Rob]

Help... I've got a set of columns that have the Date in Col A and Time in Col
B. What I'm trying to do is combine them into Col C and then have some
statistics done between Row A, B, & C. Data Below...

If I Concatenate I get this --
Col A Col B Col C
Row 1 08-21-06 10:00 AM 389500.416666666666667
-- And when I try to format the cell to look like a date and time I get
nothing.

If I use this formula -- "=A1 & " " & A2" -- I get this result --
Col A Col B Col C
Row 2 08-21-06 1:00 PM 38950 0.416666666666667
-- And I still can't format it as a date and time

This is what I want it to look like --
Col A Col B Col C
Row 3 08-22-06 10:45 AM 08-22-06 10:45 AM

If I try to Copy/Paste I get this result --
Col C
Row 1 8/21/2006 10:00
Row 2 8/21/2006 13:00
Row 3 8/22/2006 10:45
But it takes forever to copy and paste each one but I am able to format each
cell

So the whole thing should be this --
Col A Col B Col C
Row 1 08-21-06 10:45 AM 08-21-06 10:00 AM
Row 2 08-21-06 10:45 AM 08-21-06 1:00 PM
Row 3 08-22-06 10:45 AM 08-22-06 10:45 AM

This is needed because, previous to yesterday, that's how it was originally
set up and now it's going to be just the one column with both date and time,
however I need to go back and make it retro.

After I get all that done then I need to get some statistical information
between Rows 1, 2, & 3 Col C. I need the total time between each row and the
total time from all three rows, represented as hours and minutes and then the
percentage of each from the complete total. for this I've tried summing,
averaging, adding and subtracting but all I get is this type of answer...

12-05-19 9:45 AM

What I want is this...

Difference between Row 1 & 2 - 3 Hrs
Difference between Row 2 & 3 - 21 Hrs 45 Min
Total Time - 24 Hrs 45 Min

Percentage of Row 1 & 2 - 12%
Percentage of Row 2 & 3 - 88%

Is there some sort of Macro coding that I can use to achieve this or a cell
formula or what??

Sorry this was soooooo long but I wanted to be accurate and provide as much
detail as I could.
Thanks in Advance, very much.
Rob

 

[Tom Ogilvy]

C1: =A1+B1

format the cell as Date and time,
then drag down the column

 

[Tom Ogilvy]

in D2 put
=C2-C1
format with a custom format of
[h]" hours "mm" minutes"

drag fill down to D4

then in D4 change the formula to

=sum(D23)

 

[Rob]

OMG! That was sooooo simple that I feel sooooo stupid. I was making it so
darn difficult that I couldn't see the simplicity. Thank You Sooooo Much!!!!

Oh I'm embarrased to ask but, how simple would it be to get overall
percentages?

Holy-Cow I THANK YOU sooo much!
Rob

in D2 put
=C2-C1
format with a custom format of
[h]" hours "mm" minutes"

drag fill down to D4

then in D4 change the formula to

=sum(D23)

--
Regards,
Tom Ogilvy

Help... I've got a set of columns that have the Date in Col A and Time in Col
B. What I'm trying to do is combine them into Col C and then have some
statistics done between Row A, B, & C. Data Below...

If I Concatenate I get this --
Col A Col B Col C
Row 1 08-21-06 10:00 AM 389500.416666666666667
-- And when I try to format the cell to look like a date and time I get
nothing.

If I use this formula -- "=A1 & " " & A2" -- I get this result --
Col A Col B Col C
Row 2 08-21-06 1:00 PM 38950 0.416666666666667
-- And I still can't format it as a date and time

This is what I want it to look like --
Col A Col B Col C
Row 3 08-22-06 10:45 AM 08-22-06 10:45 AM

If I try to Copy/Paste I get this result --
Col C
Row 1 8/21/2006 10:00
Row 2 8/21/2006 13:00
Row 3 8/22/2006 10:45
But it takes forever to copy and paste each one but I am able to format each
cell

So the whole thing should be this --
Col A Col B Col C
Row 1 08-21-06 10:45 AM 08-21-06 10:00 AM
Row 2 08-21-06 10:45 AM 08-21-06 1:00 PM
Row 3 08-22-06 10:45 AM 08-22-06 10:45 AM

This is needed because, previous to yesterday, that's how it was originally
set up and now it's going to be just the one column with both date and time,
however I need to go back and make it retro.

After I get all that done then I need to get some statistical information
between Rows 1, 2, & 3 Col C. I need the total time between each row and the
total time from all three rows, represented as hours and minutes and then the
percentage of each from the complete total. for this I've tried summing,
averaging, adding and subtracting but all I get is this type of answer...

12-05-19 9:45 AM

What I want is this...

Difference between Row 1 & 2 - 3 Hrs
Difference between Row 2 & 3 - 21 Hrs 45 Min
Total Time - 24 Hrs 45 Min

Percentage of Row 1 & 2 - 12%
Percentage of Row 2 & 3 - 88%

Is there some sort of Macro coding that I can use to achieve this or a cell
formula or what??

Sorry this was soooooo long but I wanted to be accurate and provide as much
detail as I could.
Thanks in Advance, very much.
Rob

 

[Rob]

Nevermind, I figured that one out. Again, I was trying to make it difficult
and by using your methods I made it simple and it works wonderfully.

THANKS AGAIN!!
Rob

OMG! That was sooooo simple that I feel sooooo stupid. I was making it so
darn difficult that I couldn't see the simplicity. Thank You Sooooo Much!!!!

Oh I'm embarrased to ask but, how simple would it be to get overall
percentages?

Holy-Cow I THANK YOU sooo much!
Rob

in D2 put
=C2-C1
format with a custom format of
[h]" hours "mm" minutes"

drag fill down to D4

then in D4 change the formula to

=sum(D23)

--
Regards,
Tom Ogilvy

Help... I've got a set of columns that have the Date in Col A and Time in Col
B. What I'm trying to do is combine them into Col C and then have some
statistics done between Row A, B, & C. Data Below...

If I Concatenate I get this --
Col A Col B Col C
Row 1 08-21-06 10:00 AM 389500.416666666666667
-- And when I try to format the cell to look like a date and time I get
nothing.

If I use this formula -- "=A1 & " " & A2" -- I get this result --
Col A Col B Col C
Row 2 08-21-06 1:00 PM 38950 0.416666666666667
-- And I still can't format it as a date and time

This is what I want it to look like --
Col A Col B Col C
Row 3 08-22-06 10:45 AM 08-22-06 10:45 AM

If I try to Copy/Paste I get this result --
Col C
Row 1 8/21/2006 10:00
Row 2 8/21/2006 13:00
Row 3 8/22/2006 10:45
But it takes forever to copy and paste each one but I am able to format each
cell

So the whole thing should be this --
Col A Col B Col C
Row 1 08-21-06 10:45 AM 08-21-06 10:00 AM
Row 2 08-21-06 10:45 AM 08-21-06 1:00 PM
Row 3 08-22-06 10:45 AM 08-22-06 10:45 AM

This is needed because, previous to yesterday, that's how it was originally
set up and now it's going to be just the one column with both date and time,
however I need to go back and make it retro.

After I get all that done then I need to get some statistical information
between Rows 1, 2, & 3 Col C. I need the total time between each row and the
total time from all three rows, represented as hours and minutes and then the
percentage of each from the complete total. for this I've tried summing,
averaging, adding and subtracting but all I get is this type of answer...

12-05-19 9:45 AM

What I want is this...

Difference between Row 1 & 2 - 3 Hrs
Difference between Row 2 & 3 - 21 Hrs 45 Min
Total Time - 24 Hrs 45 Min

Percentage of Row 1 & 2 - 12%
Percentage of Row 2 & 3 - 88%

Is there some sort of Macro coding that I can use to achieve this or a cell
formula or what??

Sorry this was soooooo long but I wanted to be accurate and provide as much
detail as I could.
Thanks in Advance, very much.
Rob

 

[Tom Ogilvy]

In e2 put in

=d2/$d$4
format as Percentage

drag fill down

--
Regards,
Tom Ogilvy

OMG! That was sooooo simple that I feel sooooo stupid. I was making it so
darn difficult that I couldn't see the simplicity. Thank You Sooooo Much!!!!

Oh I'm embarrased to ask but, how simple would it be to get overall
percentages?

Holy-Cow I THANK YOU sooo much!
Rob

in D2 put
=C2-C1
format with a custom format of
[h]" hours "mm" minutes"

drag fill down to D4

then in D4 change the formula to

=sum(D23)

--
Regards,
Tom Ogilvy

Help... I've got a set of columns that have the Date in Col A and Time in Col
B. What I'm trying to do is combine them into Col C and then have some
statistics done between Row A, B, & C. Data Below...

If I Concatenate I get this --
Col A Col B Col C
Row 1 08-21-06 10:00 AM 389500.416666666666667
-- And when I try to format the cell to look like a date and time I get
nothing.

If I use this formula -- "=A1 & " " & A2" -- I get this result --
Col A Col B Col C
Row 2 08-21-06 1:00 PM 38950 0.416666666666667
-- And I still can't format it as a date and time

This is what I want it to look like --
Col A Col B Col C
Row 3 08-22-06 10:45 AM 08-22-06 10:45 AM

If I try to Copy/Paste I get this result --
Col C
Row 1 8/21/2006 10:00
Row 2 8/21/2006 13:00
Row 3 8/22/2006 10:45
But it takes forever to copy and paste each one but I am able to format each
cell

So the whole thing should be this --
Col A Col B Col C
Row 1 08-21-06 10:45 AM 08-21-06 10:00 AM
Row 2 08-21-06 10:45 AM 08-21-06 1:00 PM
Row 3 08-22-06 10:45 AM 08-22-06 10:45 AM

This is needed because, previous to yesterday, that's how it was originally
set up and now it's going to be just the one column with both date and time,
however I need to go back and make it retro.

After I get all that done then I need to get some statistical information
between Rows 1, 2, & 3 Col C. I need the total time between each row and the
total time from all three rows, represented as hours and minutes and then the
percentage of each from the complete total. for this I've tried summing,
averaging, adding and subtracting but all I get is this type of answer...

12-05-19 9:45 AM

What I want is this...

Difference between Row 1 & 2 - 3 Hrs
Difference between Row 2 & 3 - 21 Hrs 45 Min
Total Time - 24 Hrs 45 Min

Percentage of Row 1 & 2 - 12%
Percentage of Row 2 & 3 - 88%

Is there some sort of Macro coding that I can use to achieve this or a cell
formula or what??

Sorry this was soooooo long but I wanted to be accurate and provide as much
detail as I could.
Thanks in Advance, very much.
Rob