# Solved: excel forumula question

Discussion in 'Business Applications' started by mattig89ch, Apr 13, 2015.

Not open for further replies.
1. ### mattig89chThread Starter

Joined:
Dec 31, 1969
Messages:
398
Hidy ho all,

My job has me entering my clock-in times on an excel spread sheet. I then figure out exactly how long the day was, and submit that time sheet to my superior who checks my work and passes it on to god knows where.

Not exactly a difficult task, but I thought it would be awesome to figure out a forumula to have excel do this automatically.

I've gotten it most of the way there, but I'm having trouble converting the time I worked to decimals.

my formula is: (ShiftEnd - ShiftStart)-(BreakEnd-BreakStart). This produces the hours I worked in time format, but when I try to convert it to decimal it comes out to some weird decimal thats less then 1. Like .3 or something. I wanted it to come to a nice decimal place.

For example, if I work from 9AM to 5PM and take a 15 minuted break, I want the result to be 7.75 hours. Right now it resolves to 7:45, correct, but not what I'm looking for. How do I get this result to be 7.75 automatically?

2. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,371
First Name:
Wayne
multiply by 24
and format the cell as decimal

3. ### mattig89chThread Starter

Joined:
Dec 31, 1969
Messages:
398
lol, was about to post that myself.

Thanks etaf!

The formula is =((ShiftEnd-ShiftStart)-(BreakEnd-BreakStart))*24. make sure the cell you type this formula in is set to a decimal format. Otherwise you get some weird results.

One more question for the community, is there anyway to get excel to put " Hours" or " hrs" after the result?

4. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,371
First Name:
Wayne
=(((ShiftEnd-ShiftStart)-(BreakEnd-BreakStart))*24)&" Hrs"
=(((ShiftEnd-ShiftStart)-(BreakEnd-BreakStart))*24)&" Hours"

But that will change to text and so will not add up
you may need to format

=ROUND((((ShiftEnd-ShiftStart)-(BreakEnd-BreakStart))*24),2)&" Hours"

5. ### KeebellahTrusted Advisor

Joined:
Mar 27, 2008
Messages:
6,608
First Name:
Hans
Just select custom format for the cells
0.0 " Hrs"
that way it shows up as text but remains numeric and can be used in calculations

6. ### mattig89chThread Starter

Joined:
Dec 31, 1969
Messages:
398
Thanks for the help all. I have one last question, then I think I'll be good.

I want to put an if test in there, so that when i enter my time coming in, but not my time leaving, if the result is less then 0 set the cell = 0. unfortunately, the formula wizard is throwing errors. and I can only get it to have a 0 in there is I do the whole thing > 0.

My idea is something like this:
If((((EndTime-StartTime)-(BreakEnd-BreakStart))*24)&" hrs" < 0, 0, (((EndTime-StartTime)-(BreakEnd-BreakStart))*24)&" hrs"))

Edit: srry if all those parens are getting hard to follow. I'm a little lost by them myself.

7. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,371
First Name:
Wayne
If((((EndTime-StartTime)-(BreakEnd-BreakStart))*24) < 0, 0, (((EndTime-StartTime)-(BreakEnd-BreakStart))*24)&" hrs"))
remove the Hrs as that turns the result to text as mentioned

As Seen On

### Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

over 733,556 other people just like you!