Time formulas
 

Here is my timesheet:

c6: 11:00 pm
c7: 12:00 am
c8: 7:00 am
c9: 3:00 pm
c10: (Actual hours worked)

If I use the formula: =IF(C6C7,C7+1-C6,C7-C6)*24
I can get the time for c6/c7, but what formula do I use to not only get the
hours from c6/c7, but also from c8/c9 - to get the total of 9 hours in c10?

Thanks, I appreciate any help.

Time formulas
 

One way

=MOD(C9-C6,1)-MOD(C8-C7,1)


--


Regards,


Peo Sjoblom


"Blazingsaddles" wrote in message
...
Here is my timesheet:

c6: 11:00 pm
c7: 12:00 am
c8: 7:00 am
c9: 3:00 pm
c10: (Actual hours worked)

If I use the formula: =IF(C6C7,C7+1-C6,C7-C6)*24
I can get the time for c6/c7, but what formula do I use to not only get
the
hours from c6/c7, but also from c8/c9 - to get the total of 9 hours in
c10?

Thanks, I appreciate any help.

Time formulas
 

Try this:

=(MOD(C7-C6,1)+MOD(C9-C8,1))*24

--
Biff
Microsoft Excel MVP


"Blazingsaddles" wrote in message
...
Here is my timesheet:

c6: 11:00 pm
c7: 12:00 am
c8: 7:00 am
c9: 3:00 pm
c10: (Actual hours worked)

If I use the formula: =IF(C6C7,C7+1-C6,C7-C6)*24
I can get the time for c6/c7, but what formula do I use to not only get
the
hours from c6/c7, but also from c8/c9 - to get the total of 9 hours in
c10?

Thanks, I appreciate any help.

Time formulas
 

When I put in your formula, I get 9:00 am. I actually need 9.00. What
revision to the formula should I make?

Thanks.

"Peo Sjoblom" wrote:

One way

=MOD(C9-C6,1)-MOD(C8-C7,1)


--


Regards,


Peo Sjoblom


"Blazingsaddles" wrote in message
...
Here is my timesheet:

c6: 11:00 pm
c7: 12:00 am
c8: 7:00 am
c9: 3:00 pm
c10: (Actual hours worked)

If I use the formula: =IF(C6C7,C7+1-C6,C7-C6)*24
I can get the time for c6/c7, but what formula do I use to not only get
the
hours from c6/c7, but also from c8/c9 - to get the total of 9 hours in
c10?

Thanks, I appreciate any help.

Time formulas
 

=(MOD(C7-C6,1)+MOD(C9-C8,1))*24

Format the cell as General or number. This will take care of either set
crossing midnight.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Blazingsaddles" wrote in message
...
Here is my timesheet:

c6: 11:00 pm
c7: 12:00 am
c8: 7:00 am
c9: 3:00 pm
c10: (Actual hours worked)

If I use the formula: =IF(C6C7,C7+1-C6,C7-C6)*24
I can get the time for c6/c7, but what formula do I use to not only get
the
hours from c6/c7, but also from c8/c9 - to get the total of 9 hours in
c10?

Thanks, I appreciate any help.

Time formulas
 

Ugh! What am I doing wrong? C10 is 12:00 am with this formula.

Thanks.

"T. Valko" wrote:

Try this:

=(MOD(C7-C6,1)+MOD(C9-C8,1))*24

--
Biff
Microsoft Excel MVP


"Blazingsaddles" wrote in message
...
Here is my timesheet:

c6: 11:00 pm
c7: 12:00 am
c8: 7:00 am
c9: 3:00 pm
c10: (Actual hours worked)

If I use the formula: =IF(C6C7,C7+1-C6,C7-C6)*24
I can get the time for c6/c7, but what formula do I use to not only get
the
hours from c6/c7, but also from c8/c9 - to get the total of 9 hours in
c10?

Thanks, I appreciate any help.

Time formulas
 

Excellent! It worked!

Thanks for all your help. You have made my life much easier!

"Sandy Mann" wrote:

=(MOD(C7-C6,1)+MOD(C9-C8,1))*24

Format the cell as General or number. This will take care of either set
crossing midnight.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Blazingsaddles" wrote in message
...
Here is my timesheet:

c6: 11:00 pm
c7: 12:00 am
c8: 7:00 am
c9: 3:00 pm
c10: (Actual hours worked)

If I use the formula: =IF(C6C7,C7+1-C6,C7-C6)*24
I can get the time for c6/c7, but what formula do I use to not only get
the
hours from c6/c7, but also from c8/c9 - to get the total of 9 hours in
c10?

Thanks, I appreciate any help.

Time formulas
 

You asked for an answer as a number of hours, not as hours and minutes, so
don't format the answer as time, but as General or Number.
--
David Biddulph

"Blazingsaddles" wrote in message
...
Ugh! What am I doing wrong? C10 is 12:00 am with this formula.

Thanks.

"T. Valko" wrote:

Try this:

=(MOD(C7-C6,1)+MOD(C9-C8,1))*24

"Blazingsaddles" wrote in
message
...
Here is my timesheet:

c6: 11:00 pm
c7: 12:00 am
c8: 7:00 am
c9: 3:00 pm
c10: (Actual hours worked)

If I use the formula: =IF(C6C7,C7+1-C6,C7-C6)*24
I can get the time for c6/c7, but what formula do I use to not only get
the
hours from c6/c7, but also from c8/c9 - to get the total of 9 hours in
c10?

Thanks, I appreciate any help.

Time formulas
 

You need to custom format the result cell to [hh]:mm
It uses whatever regional setting (in Windows) you are using. That is why I
always change my regional settings to 24 hour.


--


Regards,


Peo Sjoblom


"Blazingsaddles" wrote in message
...
When I put in your formula, I get 9:00 am. I actually need 9.00. What
revision to the formula should I make?

Thanks.

"Peo Sjoblom" wrote:

One way

=MOD(C9-C6,1)-MOD(C8-C7,1)


--


Regards,


Peo Sjoblom


"Blazingsaddles" wrote in
message
...
Here is my timesheet:

c6: 11:00 pm
c7: 12:00 am
c8: 7:00 am
c9: 3:00 pm
c10: (Actual hours worked)

If I use the formula: =IF(C6C7,C7+1-C6,C7-C6)*24
I can get the time for c6/c7, but what formula do I use to not only get
the
hours from c6/c7, but also from c8/c9 - to get the total of 9 hours in
c10?

Thanks, I appreciate any help.

Time formulas
 

If you are going to use decimal values you need to format the result as
General or number, not time


--


Regards,


Peo Sjoblom



"Blazingsaddles" wrote in message
...
Ugh! What am I doing wrong? C10 is 12:00 am with this formula.

Thanks.

"T. Valko" wrote:

Try this:

=(MOD(C7-C6,1)+MOD(C9-C8,1))*24

--
Biff
Microsoft Excel MVP


"Blazingsaddles" wrote in
message
...
Here is my timesheet:

c6: 11:00 pm
c7: 12:00 am
c8: 7:00 am
c9: 3:00 pm
c10: (Actual hours worked)

If I use the formula: =IF(C6C7,C7+1-C6,C7-C6)*24
I can get the time for c6/c7, but what formula do I use to not only get
the
hours from c6/c7, but also from c8/c9 - to get the total of 9 hours in
c10?

Thanks, I appreciate any help.

Time formulas
 

Format the cell as GENERAL

--
Biff
Microsoft Excel MVP


"Blazingsaddles" wrote in message
...
Ugh! What am I doing wrong? C10 is 12:00 am with this formula.

Thanks.

"T. Valko" wrote:

Try this:

=(MOD(C7-C6,1)+MOD(C9-C8,1))*24

--
Biff
Microsoft Excel MVP


"Blazingsaddles" wrote in
message
...
Here is my timesheet:

c6: 11:00 pm
c7: 12:00 am
c8: 7:00 am
c9: 3:00 pm
c10: (Actual hours worked)

If I use the formula: =IF(C6C7,C7+1-C6,C7-C6)*24
I can get the time for c6/c7, but what formula do I use to not only get
the
hours from c6/c7, but also from c8/c9 - to get the total of 9 hours in
c10?

Thanks, I appreciate any help.

Time formulas
 

Thanks David, that is what I did.

"David Biddulph" wrote:

You asked for an answer as a number of hours, not as hours and minutes, so
don't format the answer as time, but as General or Number.
--
David Biddulph

"Blazingsaddles" wrote in message
...
Ugh! What am I doing wrong? C10 is 12:00 am with this formula.

Thanks.

"T. Valko" wrote:

Try this:

=(MOD(C7-C6,1)+MOD(C9-C8,1))*24

"Blazingsaddles" wrote in
message
...
Here is my timesheet:

c6: 11:00 pm
c7: 12:00 am
c8: 7:00 am
c9: 3:00 pm
c10: (Actual hours worked)

If I use the formula: =IF(C6C7,C7+1-C6,C7-C6)*24
I can get the time for c6/c7, but what formula do I use to not only get
the
hours from c6/c7, but also from c8/c9 - to get the total of 9 hours in
c10?

Thanks, I appreciate any help.

Time formulas
 

Thank you. I got it to work out. I appreciate your help.

"Peo Sjoblom" wrote:

You need to custom format the result cell to [hh]:mm
It uses whatever regional setting (in Windows) you are using. That is why I
always change my regional settings to 24 hour.


--


Regards,


Peo Sjoblom


"Blazingsaddles" wrote in message
...
When I put in your formula, I get 9:00 am. I actually need 9.00. What
revision to the formula should I make?

Thanks.

"Peo Sjoblom" wrote:

One way

=MOD(C9-C6,1)-MOD(C8-C7,1)


--


Regards,


Peo Sjoblom


"Blazingsaddles" wrote in
message
...
Here is my timesheet:

c6: 11:00 pm
c7: 12:00 am
c8: 7:00 am
c9: 3:00 pm
c10: (Actual hours worked)

If I use the formula: =IF(C6C7,C7+1-C6,C7-C6)*24
I can get the time for c6/c7, but what formula do I use to not only get
the
hours from c6/c7, but also from c8/c9 - to get the total of 9 hours in
c10?

Thanks, I appreciate any help.

Time formulas
 

Thanks for your help, I got it fixed.

"T. Valko" wrote:

Format the cell as GENERAL

--
Biff
Microsoft Excel MVP


"Blazingsaddles" wrote in message
...
Ugh! What am I doing wrong? C10 is 12:00 am with this formula.

Thanks.

"T. Valko" wrote:

Try this:

=(MOD(C7-C6,1)+MOD(C9-C8,1))*24

--
Biff
Microsoft Excel MVP


"Blazingsaddles" wrote in
message
...
Here is my timesheet:

c6: 11:00 pm
c7: 12:00 am
c8: 7:00 am
c9: 3:00 pm
c10: (Actual hours worked)

If I use the formula: =IF(C6C7,C7+1-C6,C7-C6)*24
I can get the time for c6/c7, but what formula do I use to not only get
the
hours from c6/c7, but also from c8/c9 - to get the total of 9 hours in
c10?

Thanks, I appreciate any help.

Time formulas
 

Thanks again.

"Peo Sjoblom" wrote:

If you are going to use decimal values you need to format the result as
General or number, not time


--


Regards,


Peo Sjoblom



"Blazingsaddles" wrote in message
...
Ugh! What am I doing wrong? C10 is 12:00 am with this formula.

Thanks.

"T. Valko" wrote:

Try this:

=(MOD(C7-C6,1)+MOD(C9-C8,1))*24

--
Biff
Microsoft Excel MVP


"Blazingsaddles" wrote in
message
...
Here is my timesheet:

c6: 11:00 pm
c7: 12:00 am
c8: 7:00 am
c9: 3:00 pm
c10: (Actual hours worked)

If I use the formula: =IF(C6C7,C7+1-C6,C7-C6)*24
I can get the time for c6/c7, but what formula do I use to not only get
the
hours from c6/c7, but also from c8/c9 - to get the total of 9 hours in
c10?

Thanks, I appreciate any help.

Time formulas
 

You're welcome!

--
Biff
Microsoft Excel MVP


"Blazingsaddles" wrote in message
...
Thanks for your help, I got it fixed.

"T. Valko" wrote:

Format the cell as GENERAL

--
Biff
Microsoft Excel MVP


"Blazingsaddles" wrote in
message
...
Ugh! What am I doing wrong? C10 is 12:00 am with this formula.

Thanks.

"T. Valko" wrote:

Try this:

=(MOD(C7-C6,1)+MOD(C9-C8,1))*24

--
Biff
Microsoft Excel MVP


"Blazingsaddles" wrote in
message
...
Here is my timesheet:

c6: 11:00 pm
c7: 12:00 am
c8: 7:00 am
c9: 3:00 pm
c10: (Actual hours worked)

If I use the formula: =IF(C6C7,C7+1-C6,C7-C6)*24
I can get the time for c6/c7, but what formula do I use to not only
get
the
hours from c6/c7, but also from c8/c9 - to get the total of 9 hours
in
c10?

Thanks, I appreciate any help.