Posts by slick225

I'm looking for a formula that that will subtract two cells if the value of one cell is greater than 0.

Details:
Cell B1 contains a value of 25 (Note this value never changes)
Cell B2 contains a value of 0 (Note this value will change)

I need a formula that states: if B2 is 0 display 0, but if B2 contains a number greater than 0 then subtract B2 from B1.

Re: Locking select worksheets

Thanks everyone,
that did the trick, now to train my co-workers on how to use it.

Thanks again.

Re: Locking select worksheets

I was able to protect the relevant sheets but how do you lock the cells?

I've created a spreadsheet for tracking my weekly time at work, it has serveral worksheets for summaries and totals, etc. there are about 6 worksheets total out of the 6 there are only 2 that a user should manually manipulate, all others are altered based on the info entered on the those two. I have to roll this out to my team, and want to know if there is a way I can lock the 4 worksheets that do not need manual intervention but will still function as needed (updating when the 2 worksheets are changed).

Note: Might be good to also know if I can lock portions of the editable worksheets as well, things like headers/Titles, etc. ?

Thanks in advance for any help

Re: Auto Populate Cells, can someone explain this

Thanks Andy and Derk, that explained it all.
I've applied the formula to a few other projects and with some teaking it should work perfectly.

Thanks again.

My department is using an excel file to generate an XML file, and the formula used, I would like to use in other potential excel projects, could someone please explain this formula?

Sample Formula: Cell 1: =" Can Someone " &Cell 2 &" help me with this"
Cell 2: Please

The formula replaces the Cell reference in Cell 1 with what ever value is in Cell 2.

Please see sample file for a better example.

I need a new formula that looks at a couple of criterias before calculating.

This is a spreadsheet I developed for work, it helps me track my time and estimated times for projects. (See Attached Spreadsheet)

column explanations
A7-A11 are the estimates for my projects on a weekly bases.
B7-B11 are my Project #s.
C7-C11 are formulas that display how much estimated project is left. D7-D11 displays any overtime I needed on a project.

G3-G9 display the weekday totals.
I3-I7 display the weekly project time.
J4, J6, and J8 display miscellaneous project time totals.

Rows 15 and below is where I store my data.

My new feature: M2

This area will total up the amount of time spent on a project for each day.
This will save me time when inputing my data in the official tracking system, b/c I won't have to filter and add up times.

My new Problem: I need a formula that looks at A15-A90 (the date), and then looks at the Project # (H15-H90) and adds the Hours (B15-B90).

Expected:
The expected results should be a total number of hours for each day per project.

Thanks in advance for your help.

Sample Attached.

Also a manual task I must perform each week is modifying the dates in G3-G9 to reflect the current week, is there a formula that will automatically update the dates for me? and, I hate to be too picky but I back these up at the beginning of the new week (copying entire worksheet to another worksheet and delete the data and change the dates of the original), so I would need a formula or macro that I could run a to update to the current week, an persistant formula would also update my history saves.

Again, your help and expertise is greatly appreciated.

EDIT: I have edited the subject title for you - jiuk

Re: Time tracking using a description, can it be done?

Thanks Derk, That worked

Hello everyone,

We use a time tracking program at work.

This program has a very low timeout setting, (around 2 or 3 minutes), causing me to logon several times to complete a timesheet.

I created a spreadsheet similar to the one attached, it has a total hrs of 40 and a remaining hrs (basically Cell D1 starts at 40hrs and when a number is entered in the hours column that number is subtracted from 40 leaving the hours remaining).

My problem: I also have a set amount of time I can enter for my projects (budget reasons) in the sample I've put in cell G5 the number 26, I need a formula that will subtract from 26 the hours that has a particular description, in my sample I using Description 2.

When the Text Description 2 is enter in the C column, the corresponding hours should be subtracted from G5. In my sample, B3, B5, B6, and B9 should be subtracted from G5.

Hope I've provided enought Information.

Thanks in advance for your help.

Thanks WillR, that did give me the desired results.

Could you give me a brief description of the formula.
=DATEDIF(C2,TODAY(),"d")

My Assumption is "d" means format Day

Problem Date calculations (cont') Part 2

This problem has returned now when using both =Now() and =Today().

When I originally posted the issue chaning the formula to =TODAY() resolved the problem (The result was no longer 01 it was 31 as it should be, but after that day the results were 02 and I've tried both NOW and TODAY in the formula and cannot get the desired results.

Thanks in advance for any help

Attached is a sample of the current formula.

Thanks Timbo, that did the trick

I created a formula that calculates the Start Date and todays date, the formula's goal was to calculate the amount of days from the start date. This formula appeared to be working fine until recently when I checked the stat and is displayed 01 as the number of days.

Formula:
=NOW()-(F4)

F4 is the cell that contains the start date and time 9/10/04 8:00 AM

The Cell format was 'dd', I also tried 'mm dd' and 'yy mm dd'

When I added 'mm' to the cell the month was displayed as 02 which is also incorrect.

Thanks in advance for any help, I'll try to attach a sample of the formula.

Can you share a word doc like you can an Excel spreadsheet on a network drive?

You can share an Excel spreadsheet on a network and several people can update it at one time, without getting a read-only notification, all updates are updated when someone saves and the save only effect the modifed fields, so you won't be over writing someone elses update with blank cells. My question is can you share a word doc that has been setup with a table the same way?

ctrolz & thomach, where would this formula be applied in a real world scenario, I'd like to understand the formula but without the why, I'm a little lost.

Dangelor, I did some playing around with the formatting and tried this and it accomplished exactly what I needed custom format: dd hh:mm:ss

actually what I did was use your formula in two cells one to calculate the # of days and the other to calculate hours, minutes, and seconds.

Thanks again, you were a big help

Thanks dangelor, that did the trick

Dangelor this is the formula I'm using =NOW()-(F4)
F4 contains my startdate/time 9/10/2004 8:00 AM

Results: the formula seems to calculate the time elapsed from 8:00 but not from 8:00 on the 10th, it calculate the elapsed time from 8:00 of each day.

how should the cell A1 and A2 be formatted or does it matter?

Is there a formula that will calculate the amount of time from a start point till current day.

Example: I want to enter a start date and time, and in another cell I want the amount of time that has elapsed between the start date and time till the current date and time, something like: 90 day, 6 hours, and 23 minutes or even 180 hours and 23 minutes or maybe just 180 hours would do.