Home Articles Forums Gallery Marketplace Videos

WoodCentral Forums

Knowledge You Can Build On — Est. 1998

Fast and affordable laser cutting – so you can make the parts you can’t find anywhere else

excel formula help

Posts

excel formula help

#1

excel formula help

Chuck in Mtl.

will this formula work?

=IF(G3-TODAY

does not seem to as it is..

Re: excel formula help

#2

Bob

Re: excel formula help

Bob

What are you trying to do?

Re: excel formula help

#3

Re: excel formula help

Chuck in Mtl.

I am trying to subtract the current date using TODAY from another date and have the result in days which in then compared to a number to know if an instrument is still in calibration or is due for calibration.

Re: excel formula help

#4

Bob

Re: excel formula help

Bob

I would have to look it up Microsoft has a great help section accessed from the help menu.

Re: excel formula help

#5

Re: excel formula help

mdclor

Chuck, convert all your dates using the datevalue function. For example, enter =DATEVALUE("7/28/2012") in a cell for today, July 28, 2012. Excel returns the value of 41118. Then enter =DATEVALUE("7/27/2012") for yesterday (or all your dates of interest) and the program returns 41117. Subtracting yesterday's value from today's yields 1. Let me know if you have any questions.

Re: excel formula help

#6

Re: excel formula help

Chuck in Mtl.

will this work if I need to always have the current date?

Re: excel formula help

#8

Re: excel formula help - TODAY

Chuck in Mtl.

thanks for the link...another question....how can i copy formulas so that the cell reference changes relative to the other cells? Right now it seems i have to type in the correct cell reference each time.

Re: excel formula help

#9

Re: excel formula help - TODAY

John McGaw

Typically in all spreadsheets (I'm not an Excel user but I'm sure they are the same) the cell references change automatically during the copy process. The real problem comes when you don't want them to change. You should look up the '!' in the help files to see how to anchor rows, columns, or both -- anything that is not anchored will be changed according to the distance and direction the copy is done over.

Re: excel formula help

#10

Re: excel formula help

Rick Christopherson

If the cells are formatted to display dates, then it is not necessary to convert the date to a number. Excel will do this automatically. I have a spreadsheet for tracking my billable hours, and at the top of the sheet I enter the string 7/28/12, and excel displays this as July 28, 2012. However, this is actually an ending-date, so it is 2 weeks in the future. I then subtract from this date to get each of the dates for the respective entries in this "timecard" sheet. Those cells are also formatted for dates, so they display shorter date versions of Sat:7/28.

Even though each of these entries is a string (the 7/28/12), excel knows they are dates and does not perform division. It internally treats this string as a date number, even though it shows the string. If I had entered =7/28/12, then excel would have done the division.

If you wanted to find out how many days it has been since the passage of the date shown in a cell (G3), then put in =Today()-G3, and it will display a number representing the days. Just make sure G3 is formatted for a date not a number or text.

If you wanted to flag something as expired, then use =if(Today()-G3>6,"expired","not expired"). Anything greater than 6 days will show "expired". Anything less than or equal to 6 (including negative numbers) will show "not expired".

Re: excel formula help

#11

Re: excel formula help - TODAY

Rick Christopherson

Excel automatically copies formulas with a relative reference. If you select a cell and then copy it downward, the referenced row number in your formula will be incremented by 1 each time. If you copied it right/left, then the column will increment.

However, if you "move" a cell entry (i.e. cut/paste or drag/drop), then the reference remains fixed to the original cell.

The trick is when you want to copy a cell but keep all or part of the reference fixed. This uses the "$".

In a formula, $G$3 will always reference G3 no matter where the formula gets copied or moved. "G$3" will always reference the 3rd row, but the "G" column will increment if copied sideways. With your editing cursor in the formula on the cell reference (e.g. between G and 3), press the F4 function key and it will cycle through all of the "$" options ($G$3, G$3, $G3, G3).

By the way, if you move the target (e.g. move the contents of G3 to G4) then all equations that referenced the contents of G3 will automatically change reference to G4. But if you copy the contents of G3 to G4, then all references remain unchanged, and still point to G3.

Re: excel formula help

#12

Oops, possible addition

Rick Christopherson

I'm using an older version of Excel because I do not like what the newer versions of Office are doing. I see from another thread that possibly this "$" function has been replaced by the "!" character.

Re: excel formula help

#13

Bob

Re: excel formula help

Bob

10 years ago I was a Microsoft partner and Office power user. sorry but I have forgotten so much. I use ver 10 now but its like I never used it before. Time flies I will leave the answers to the ones that still have a brain for other than wood working. LOL

Re: excel formula help

#14

Re: Oops, possible addition

John McGaw

No, you are right. It is '$' as in $E$17 and '!' doesn't come into it -- a mental slip on my part.