end - start less one hour for lunch

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have gotten some great tips from this forum and have been to the site
http://www.mvps.org/dmcritchie/excel/datetime.htm which was very helpful.
But - can someone give me the quick and painless way to calculate the
difference in time less one hour (lunch).
I am currently using =E9-C9+(C9>E9) and formatted it to [h]:mm:ss , then
having a cell next door to it to calculate it into decimal hours (F9*24-1)
really...
but how do I get that (-1 hour) into the cell that calculates the
hours/minutes? I have tried tagging a -1:00 on the end or just a -1 but I
get the ole #VALUE error.

Thanks in advance - I know this is likely easy...

Michelle
 
mochamichelle said:
I have gotten some great tips from this forum and have been to the site
http://www.mvps.org/dmcritchie/excel/datetime.htm which was very helpful.
But - can someone give me the quick and painless way to calculate the
difference in time less one hour (lunch).
I am currently using =E9-C9+(C9>E9) and formatted it to [h]:mm:ss , then
having a cell next door to it to calculate it into decimal hours (F9*24-1)
really...
but how do I get that (-1 hour) into the cell that calculates the
hours/minutes? I have tried tagging a -1:00 on the end or just a -1 but I
get the ole #VALUE error.
Click to expand...

=E9-C9+(C9>E9)-TIME(1,0,0)
or
=E9-C9+(C9>E9)-1/24
 
Michelle,

try this:

=E9-C9-1/24+(C9>E9)

as these are times, which are stored as fractions of a 24-hour day, you
need to subtract 1/24th for an hour. Your other formula would now
become:

=F9*24

Hope this helps.

Pete
 
Thank you so much for your reply

Now - I have an issue - if there is no time entered, my Duration cells are a
negative number! (and show as ########)
is there a way to say this number must be a positive number or else it is zero

Thanks!

Michelle

David Biddulph said:
mochamichelle said:
I have gotten some great tips from this forum and have been to the site
http://www.mvps.org/dmcritchie/excel/datetime.htm which was very helpful.
But - can someone give me the quick and painless way to calculate the
difference in time less one hour (lunch).
I am currently using =E9-C9+(C9>E9) and formatted it to [h]:mm:ss , then
having a cell next door to it to calculate it into decimal hours (F9*24-1)
really...
but how do I get that (-1 hour) into the cell that calculates the
hours/minutes? I have tried tagging a -1:00 on the end or just a -1 but I
get the ole #VALUE error.
Click to expand...

=E9-C9+(C9>E9)-TIME(1,0,0)
or
=E9-C9+(C9>E9)-1/24
Click to expand...
 
mochamichelle said:
David Biddulph said:
message
I have gotten some great tips from this forum and have been to the site
http://www.mvps.org/dmcritchie/excel/datetime.htm which was very
helpful.
But - can someone give me the quick and painless way to calculate the
difference in time less one hour (lunch).
I am currently using =E9-C9+(C9>E9) and formatted it to [h]:mm:ss ,
then
having a cell next door to it to calculate it into decimal hours
(F9*24-1)
really...
but how do I get that (-1 hour) into the cell that calculates the
hours/minutes? I have tried tagging a -1:00 on the end or just a -1
but I
get the ole #VALUE error.
=E9-C9+(C9>E9)-TIME(1,0,0)
or
=E9-C9+(C9>E9)-1/24
Click to expand...
Click to expand...

Thank you so much for your reply

Now - I have an issue - if there is no time entered, my Duration cells are
a
negative number! (and show as ########)
is there a way to say this number must be a positive number or else it is
zero
Click to expand...

If you want to show negative times, you could use Tools/ Options/
Calculation, and select 1904 date system,

but otherwise if you want to limit to a minimum of zero you can use
=MAX(0,E9-C9+(C9>E9)-1/24)

or if you want to leave the output blank if either of the inputs is empty,
you could use
=IF(OR(ISBLANK(C9),ISBLANK(E9)),"",E9-C9+(C9>E9)-1/24)
 
Back
Top