Hi
Anyone know a shortcut to putting dollar signs in formulas like converting this "=SUMIF(I5:I44,B51:B59,G5:G44)" to this "=SUMIF(I$5:I$44,B$51:B$59,G$5:G$44)"

Highlight the part you want and then press F4

Give that man a medal - why didn't I ask this question years ago

For the dummies amongst us each time you push F4 it changes where the $ sign appears.

[Edited on 25/2/14 by Ivan]

Here's another couple of short cuts I use all the time .....

To format cells to currency - highlight the cells or column then press <ctrl> <shift> 4
(4 being the dollar sign)


This also works with %
<ctrl> <shift> 5

Thanks - will remember that

Use this amost as much as 'ALT' and 'Print Screen'.

copies current 'Active Window' to clipboard rather than Full page ( whish is sisable on twin 19 inch monitors ).

Reagrds

Agriv8

Just google excel shortcuts and there are thousands!

Anyone help me with a formula for time = hours?

I'm after a formula that takes the human error out of this..

18.00 to 07.30 -45mins = ?? Answer is obviously 12.75 but is there a way to do it automatic like?

quote:

---

Originally posted by Barkalarr
Here's another couple of short cuts I use all the time .....

To format cells to currency - highlight the cells or column then press <ctrl> <shift> 4
(4 being the dollar sign)


This also works with %
<ctrl> <shift> 5

---

CTRL + SHIFT + ~

Shifts format back to general.

So i have this formula that gives me total hours worked minus a 1 hour break, BUT in the decimal..

=SUM(G21-F21,1)-IF(WEEKDAY(C21,1)>0,1/24) = 0.46875

I need to just times by 24 to get the paid 11.25 hours for the result but it gives me an error if I put *24 on the end or in the middle..

Any advise for multiplying the formula?

^^ not sure what the format of your inputs are to this are in so tell me if I've misunderstood anything...

=SUM(G21-F21,1) = will give you 1 + Cell G21 - Cell F21 - that doesn't sound right to me, what is in these cells ?

WEEKDAY(C21,1) = will give you a 1 for sunday, 2 for monday..... and 7 for saturday

-IF(WEEKDAY(C21,1)>0,1/24) = if weekday is greater than 0 (which it always is) then subtract 0.0416777


this means that your taking a difference of two different times and adding 1 to it, then whatever the day of the week it will always subtract 1/24th off the answer ??? tell me if I've missed something here

you may want to change to the other versions of weekday: like 2 instead of 1 so that sat/sun become 6 & 7 and you can just say if weekday < 6 then subtract 1 hour
otherwise you'll need a if weekday is between 1 and 7 style logic function instead



dates and times are a bit funny in excel... Ive never really got my head around how the stupid serial number thing works

if it helps any you can turn a number of hours, minutes and seconds into the excel decimal format using the =TIME(Hour,Min,Sec) which you can then just format as time to display it correctly
but it doesn't like fractions of an hour in the hour section so you need to do a bit of pre processing to feed it integers in each section....
(check the help file for the limitations & syntax of these functions)

I use this for my hours:
=TEXT(TIME((INT(AP5)),(60*(AP5-(INT(AP5)))),0), "hh:mm"
its working on a decimal sum of hours worked for each job in cell AP5 (I use multiple columns to add up across multiple start stop jobs each day)

for whatever reason I decided to use the TEXT function to format the result as 'hh:mm', but its so long ago I can't remember why I did this rather than just format the cell..... I was probably planing to add something else that never happened

then the next bit make turns the hours and mins into an excel format time

the middle bits give the hours which are just the integer (INT) of the decimal time, and the min's which are the decimal minus the integer and multiply by 60



but you may be better off avoiding excels decimal time stuff altogether and just working with the hours without telling excel thats what they are - if you add up in hours rather than fractions of 24 then

that should give you some ideas to play with



[Edited on 26/2/2014 by mcerd1]

Forget all that, after sleeping on it came up with a much simpler way to determine shift length with breaks taken off...

=MOD(G21-F21,1)-H21

Where F21 is start time, G21 is end time and H21 is break length. These cells all formatted to time so 18:00 0:45 etc..