Enter Standard Time for H:MM Formula

[Guest]

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.

 

[PaulD]

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

: 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.
:
:
:

 

[Guest]

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

 

[Guest]

=A2-A1+(A1>A2)
format as time

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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

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

--
Regards,
Tom Ogilvy

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

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.

 

[Guest]

It's giving me 2.48

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

=A2-A1+(A1>A2)
format as time

 

[Guest]

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

 

[Guest]

Look again,

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

--
Regards,
Tom Ogilvy

It's giving me 2.48

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

=A2-A1+(A1>A2)
format as time

 

[Guest]

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

 

[Guest]

format as numbers

"excelent" skrev:

 

[Guest]

It gives me an error message when I enter this.

 

[Guest]

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



"Benz" skrev:

 

[Guest]

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

 

[Guest]

he probably meant this:

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

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

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

 

[Guest]

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

the Danish word for Hour is Time? Interesting.

 

[Guest]

Send me a sample worksheet to (e-mail address removed) (with a valid return address)


or give me your email and I will send you something with all of them working.

 

[Guest]

I sent you an e-mail from benzNbent@hotmail

Send me a sample worksheet to (e-mail address removed) (with a valid return address)


or give me your email and I will send you something with all of them working.