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

Thread 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?
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,484
multiply by 24
and format the cell as decimal
 

mattig89ch

Thread 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?
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,484
=(((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
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,639
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

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

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,484
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.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
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.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top