# Solved: excel forumula question

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

#### mattig89ch

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?

#### etaf

Wayne
Moderator
multiply by 24
and format the cell as decimal

#### mattig89ch

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?

#### etaf

Wayne
Moderator
=(((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"

#### Keebellah

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

#### mattig89ch

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.

#### etaf

Wayne
Moderator
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

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

As Seen On