Enter Standard Time for H:MM Formula
 

To compute time spent on hourly projects, I need to know formula that will
allow me to enter standard time and have it find the difference

ie/
(Start)A1: 4:00
(End)A2: 6:48
(Total)A3: 2.8

It sounds so easy just subtract the time and divide the Minutes by 60 but I
just cant get it. Plz help! Thank You.

Enter Standard Time for H:MM Formula
 

I've used this before
(Start)A1: 4:00
(End)A2: 6:48
(Total)A3: =IF(ISBLANK(A2),"",(A2-A1)*24)

Cells A1 and A2 are using a time type 13:30 format
Paul D

"Benz" wrote in message
...
: To compute time spent on hourly projects, I need to know formula that will
: allow me to enter standard time and have it find the difference
:
: ie/
: (Start)A1: 4:00
: (End)A2: 6:48
: (Total)A3: 2.8
:
: It sounds so easy just subtract the time and divide the Minutes by 60 but
I
: just cant get it. Plz help! Thank You.
:
:
:

Enter Standard Time for H:MM Formula
 

a3: = a2-A1

format the cell as [mm]:00 for the time difference in minutes.

the time you show is in hours and minutes, so to show hours and minutes,
format as [h]:00

--
Regards,
Tom Ogilvy


"Benz" wrote:

To compute time spent on hourly projects, I need to know formula that will
allow me to enter standard time and have it find the difference

ie/
(Start)A1: 4:00
(End)A2: 6:48
(Total)A3: 2.8

It sounds so easy just subtract the time and divide the Minutes by 60 but I
just cant get it. Plz help! Thank You.

Enter Standard Time for H:MM Formula
 

=A2-A1+(A1A2)
format as time

Enter Standard Time for H:MM Formula
 

A1 and A2 can use any time format. This will give you hours with a decimal.
so 30 minutes would be .5. If you wanted minutes (and it isn't clear what
you want), then just multiply by 60.

=IF(ISBLANK(A2),"",(A2-A1)*24*60)

Note that time is stored as a decimal portion of a 24 hour day.
--
Regards,
Tom Ogilvy


"PaulD" wrote:

I've used this before
(Start)A1: 4:00
(End)A2: 6:48
(Total)A3: =IF(ISBLANK(A2),"",(A2-A1)*24)

Cells A1 and A2 are using a time type 13:30 format
Paul D

"Benz" wrote in message
...
: To compute time spent on hourly projects, I need to know formula that will
: allow me to enter standard time and have it find the difference
:
: ie/
: (Start)A1: 4:00
: (End)A2: 6:48
: (Total)A3: 2.8
:
: It sounds so easy just subtract the time and divide the Minutes by 60 but
I
: just cant get it. Plz help! Thank You.
:
:
:

Enter Standard Time for H:MM Formula
 

When I enter in this formula it gives me 0:00. So the easy part is the the
hours, the part that never comes out right is the minutes (48/60) to give a
fraction of an hour. Does that kind of make sense? I'm not that expereinced
with Excel so bare with me here.

"Tom Ogilvy" wrote:

A1 and A2 can use any time format. This will give you hours with a decimal.
so 30 minutes would be .5. If you wanted minutes (and it isn't clear what
you want), then just multiply by 60.

=IF(ISBLANK(A2),"",(A2-A1)*24*60)

Note that time is stored as a decimal portion of a 24 hour day.
--
Regards,
Tom Ogilvy


"PaulD" wrote:

I've used this before
(Start)A1: 4:00
(End)A2: 6:48
(Total)A3: =IF(ISBLANK(A2),"",(A2-A1)*24)

Cells A1 and A2 are using a time type 13:30 format
Paul D

"Benz" wrote in message
...
: To compute time spent on hourly projects, I need to know formula that will
: allow me to enter standard time and have it find the difference
:
: ie/
: (Start)A1: 4:00
: (End)A2: 6:48
: (Total)A3: 2.8
:
: It sounds so easy just subtract the time and divide the Minutes by 60 but
I
: just cant get it. Plz help! Thank You.
:
:
:

Enter Standard Time for H:MM Formula
 

That is certainly more robust, handling differences across midnight as well.
Similarly the more compact:

=Mod(A2-A1,1)

format as [h]:mm I would think.

--
Regards,
Tom Ogilvy




"excelent" wrote:

=A2-A1+(A1A2)
format as time

Enter Standard Time for H:MM Formula
 

sorry for the error on those formats
[mm]:00 should be [mm]:ss

and
[h]:00 should be [h]:mm

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote:

a3: = a2-A1

format the cell as [mm]:00 for the time difference in minutes.

the time you show is in hours and minutes, so to show hours and minutes,
format as [h]:00

--
Regards,
Tom Ogilvy


"Benz" wrote:

To compute time spent on hourly projects, I need to know formula that will
allow me to enter standard time and have it find the difference

ie/
(Start)A1: 4:00
(End)A2: 6:48
(Total)A3: 2.8

It sounds so easy just subtract the time and divide the Minutes by 60 but I
just cant get it. Plz help! Thank You.

Enter Standard Time for H:MM Formula
 

It's giving me 2.48

"Tom Ogilvy" wrote:

That is certainly more robust, handling differences across midnight as well.
Similarly the more compact:

=Mod(A2-A1,1)

format as [h]:mm I would think.

--
Regards,
Tom Ogilvy




"excelent" wrote:

=A2-A1+(A1A2)
format as time

Enter Standard Time for H:MM Formula
 

If I use PaulD's original formula with the values you show stored as dates
(A1 and A2), and format A3 as General I get:

2.8

--
Regards,
Tom Ogilvy


"Benz" wrote:

When I enter in this formula it gives me 0:00. So the easy part is the the
hours, the part that never comes out right is the minutes (48/60) to give a
fraction of an hour. Does that kind of make sense? I'm not that expereinced
with Excel so bare with me here.

"Tom Ogilvy" wrote:

A1 and A2 can use any time format. This will give you hours with a decimal.
so 30 minutes would be .5. If you wanted minutes (and it isn't clear what
you want), then just multiply by 60.

=IF(ISBLANK(A2),"",(A2-A1)*24*60)

Note that time is stored as a decimal portion of a 24 hour day.
--
Regards,
Tom Ogilvy


"PaulD" wrote:

I've used this before
(Start)A1: 4:00
(End)A2: 6:48
(Total)A3: =IF(ISBLANK(A2),"",(A2-A1)*24)

Cells A1 and A2 are using a time type 13:30 format
Paul D

"Benz" wrote in message
...
: To compute time spent on hourly projects, I need to know formula that will
: allow me to enter standard time and have it find the difference
:
: ie/
: (Start)A1: 4:00
: (End)A2: 6:48
: (Total)A3: 2.8
:
: It sounds so easy just subtract the time and divide the Minutes by 60 but
I
: just cant get it. Plz help! Thank You.
:
:
:

Enter Standard Time for H:MM Formula
 

Look again,

It gives me 2:48, 2 hours and 48 minutes.

--
Regards,
Tom Ogilvy


"Benz" wrote:

It's giving me 2.48

"Tom Ogilvy" wrote:

That is certainly more robust, handling differences across midnight as well.
Similarly the more compact:

=Mod(A2-A1,1)

format as [h]:mm I would think.

--
Regards,
Tom Ogilvy




"excelent" wrote:

=A2-A1+(A1A2)
format as time

Enter Standard Time for H:MM Formula
 

Unless u realy want minutes as dec.
=(TIME(A2-A1+(A1A2))&","&(MINUTE(A2-A1+(A1A2))/60*100))*1

Enter Standard Time for H:MM Formula
 

format as numbers

"excelent" skrev:

Unless u realy want minutes as dec.
=(TIME(A2-A1+(A1A2))&","&(MINUTE(A2-A1+(A1A2))/60*100))*1

Enter Standard Time for H:MM Formula
 

It gives me an error message when I enter this.

"excelent" wrote:

Unless u realy want minutes as dec.
=(TIME(A2-A1+(A1A2))&","&(MINUTE(A2-A1+(A1A2))/60*100))*1

Enter Standard Time for H:MM Formula
 

Sry. was in dannish
=(HOUR(A2-A1+(A1A2))&","&(MINUTE(A2-A1+(A1A2))/60*100))*1



"Benz" skrev:

It gives me an error message when I enter this.

"excelent" wrote:

Unless u realy want minutes as dec.
=(TIME(A2-A1+(A1A2))&","&(MINUTE(A2-A1+(A1A2))/60*100))*1

Enter Standard Time for H:MM Formula
 

he probably meant this:

=(HOUR(A2-A1+(A1A2))&"."&(MINUTE(A2-A1+(A1A2))/60*100))*1

change the "." to "," or whatever your decimal separator is.

However, the Mod(A2-A1,1) or A2-A2+(A1A2) either formatted as general
should be sufficient to return the same result).

--
Regards,
Tom Ogilvy


"Benz" wrote:

It gives me an error message when I enter this.

"excelent" wrote:

Unless u realy want minutes as dec.
=(TIME(A2-A1+(A1A2))&","&(MINUTE(A2-A1+(A1A2))/60*100))*1

Enter Standard Time for H:MM Formula
 

Am I doing something wrong? I've tried everything everyone of you have so
nicely suggested and nothing has worked.

"Benz" wrote:

To compute time spent on hourly projects, I need to know formula that will
allow me to enter standard time and have it find the difference

ie/
(Start)A1: 4:00
(End)A2: 6:48
(Total)A3: 2.8

It sounds so easy just subtract the time and divide the Minutes by 60 but I
just cant get it. Plz help! Thank You.

Enter Standard Time for H:MM Formula
 

the comma will still give problems anywhere comma isn't the decimal
separator.

the Danish word for Hour is Time? Interesting.

--
Regards,
Tom Ogilvy


"excelent" wrote:

Sry. was in dannish
=(HOUR(A2-A1+(A1A2))&","&(MINUTE(A2-A1+(A1A2))/60*100))*1



"Benz" skrev:

It gives me an error message when I enter this.

"excelent" wrote:

Unless u realy want minutes as dec.
=(TIME(A2-A1+(A1A2))&","&(MINUTE(A2-A1+(A1A2))/60*100))*1

Enter Standard Time for H:MM Formula
 

Send me a sample worksheet to (with a valid return address)


or give me your email and I will send you something with all of them working.
--
Regards,
Tom Ogilvy


"Benz" wrote:

Am I doing something wrong? I've tried everything everyone of you have so
nicely suggested and nothing has worked.

"Benz" wrote:

To compute time spent on hourly projects, I need to know formula that will
allow me to enter standard time and have it find the difference

ie/
(Start)A1: 4:00
(End)A2: 6:48
(Total)A3: 2.8

It sounds so easy just subtract the time and divide the Minutes by 60 but I
just cant get it. Plz help! Thank You.

Enter Standard Time for H:MM Formula
 

I sent you an e-mail from benzNbent@hotmail

"Tom Ogilvy" wrote:

Send me a sample worksheet to (with a valid return address)


or give me your email and I will send you something with all of them working.
--
Regards,
Tom Ogilvy


"Benz" wrote:

Am I doing something wrong? I've tried everything everyone of you have so
nicely suggested and nothing has worked.

"Benz" wrote:

To compute time spent on hourly projects, I need to know formula that will
allow me to enter standard time and have it find the difference

ie/
(Start)A1: 4:00
(End)A2: 6:48
(Total)A3: 2.8

It sounds so easy just subtract the time and divide the Minutes by 60 but I
just cant get it. Plz help! Thank You.

Enter Standard Time for H:MM Formula
 

Just for completeness:
And I replied. You responded that what I sent appeared satisfactory.

--
Regards,
Tom Ogilvy

"Benz" wrote:

I sent you an e-mail from benzNbent@hotmail

"Tom Ogilvy" wrote:

Send me a sample worksheet to (with a valid return address)


or give me your email and I will send you something with all of them working.
--
Regards,
Tom Ogilvy


"Benz" wrote:

Am I doing something wrong? I've tried everything everyone of you have so
nicely suggested and nothing has worked.

"Benz" wrote:

To compute time spent on hourly projects, I need to know formula that will
allow me to enter standard time and have it find the difference

ie/
(Start)A1: 4:00
(End)A2: 6:48
(Total)A3: 2.8

It sounds so easy just subtract the time and divide the Minutes by 60 but I
just cant get it. Plz help! Thank You.

Enter Standard Time for H:MM Formula
 

Yes, You did a wonderful job in helping me. :)

"Tom Ogilvy" wrote:

Just for completeness:
And I replied. You responded that what I sent appeared satisfactory.

--
Regards,
Tom Ogilvy

"Benz" wrote:

I sent you an e-mail from benzNbent@hotmail

"Tom Ogilvy" wrote:

Send me a sample worksheet to (with a valid return address)


or give me your email and I will send you something with all of them working.
--
Regards,
Tom Ogilvy


"Benz" wrote:

Am I doing something wrong? I've tried everything everyone of you have so
nicely suggested and nothing has worked.

"Benz" wrote:

To compute time spent on hourly projects, I need to know formula that will
allow me to enter standard time and have it find the difference

ie/
(Start)A1: 4:00
(End)A2: 6:48
(Total)A3: 2.8

It sounds so easy just subtract the time and divide the Minutes by 60 but I
just cant get it. Plz help! Thank You.