How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask NewITZone Your Own Question
NewITZone
NewITZone, Computer Enthusiast
Category: Microsoft Office
Satisfied Customers: 90
Experience:  I have been using Office Pro for 10 years and implemented applications for business use.
53509759
Type Your Microsoft Office Question Here...
NewITZone is online now
A new question is answered every 9 seconds

Excel question

Customer Question

Excel question
Submitted: 4 months ago.
Category: Microsoft Office
Customer: replied 4 months ago.
I made a spreadsheet to figure biweekly accrued time now what I want to do is add that time to a cell to update current vac and personal time.
Expert:  Russell H. replied 4 months ago.

Hi, thank you for contacting JustAnswer.com. My name is Russell. I will do my best to provide the right answer to your question.

OK. I can answer that question easily.

Either you can tell me what cell the biweekly accrued time is in (e.g. C2, F15) and what cell you want to add that time to... or you could post me the 'sheet, and I can insert the formula you want where you want it.

Customer: replied 4 months ago.
B5, B6 show current vac and personal time balances they currently are entered manually, I need the bimonthly accrued time automatically added to B5 and B6. In H7 the weeks of biweekly pay periods are counted down, one suggestion would be that as that number goes down it adds vac 4.6 and per 1.53 accrued time to B5,B6 respectfully. Another words I want the cells B5 and B6 to reflect the bimonthly time accrued.
Customer: replied 4 months ago.
I should be able to add the formula to the sheet myself once you create it. Thanks
Customer: replied 4 months ago.
I attached the sheet
Expert:  Russell H. replied 4 months ago.

You cannot, in Excel, both add a quantity to a cell and have the cell value entered manually.

A value entered manually in one cell, can be added to another value and the sum can be put in another cell (or two cell's values can be entered manually, added, and the sum put in another cell. Or, a cell value entered manually can be added to another formula-calculated value in another cell, and the sum placed in a third cell.)

So which cells of the ones you mentioned - B5, B6 (Vac time, Personal Time, for employee), and H7 (weeks of Biweekly pay-periods, and is that entered manually?...) - would have calculated time put in by formula, and which ones would have their amounts entered manually?

(Perhaps you're thinking of a script which would be run periodically, not a formula?)

Customer: replied 4 months ago.
I want to get away from entering manually I want it automatically via formula. Currently cells B5 and B6 are entered manually based on paycheck stub information.
Customer: replied 4 months ago.
The time is accrued bimonthly
Expert:  Russell H. replied 4 months ago.

OK... A formula cannot be 'timed' or periodic, and a script would be in VB language, which I am not good at.

Since what you need is really a VB script to be run periodically, but otherwise to serve the purpose of a formula, I am Opting Out, opening the case to other Experts in this category - a VB Expert will take this up.

Customer: replied 4 months ago.
The time in H7 is periodic based on biweekly
Customer: replied 4 months ago.
I don't want a VB script, I want a formula like what is in H7
Expert:  bbao replied 4 months ago.

Let me review the question.

Customer: replied 4 months ago.
I've attached a spread sheet, it lets me know how much vacation and personal time I have left by the end of year. As you can see I accrue 4.6hr vac and 1.53 Personal time on a bimonthly basis. I been able to come up with a formula to figure bimonthly pay periods left in the year but I would like for that information to automatically update the cells B5 and B6 as it counts down the weeks then it adds accrued time to B5 and B6 totals. I want a formula to do this.
Expert:  bbao replied 4 months ago.

Thanks.

Expert:  bbao replied 4 months ago.

Okay, basically you need the formulas for B5 and B6. Right?

Can you do a manual calculation for B5 and B6 in order to demonstrate how 67.8 ans 23.7 are calculated? I need to confirm the details. Thanks.

Customer: replied 4 months ago.
I have been updating the hours in cells B5 and B6 manually based on check stub information in order to calculate year end results. I need a way to add bimonthly accrued time to those totals so it does it automatically, is there a way to increase B5 as H5 decreases and increase B6 as H6 decreases by the same amounts.
Expert:  bbao replied 4 months ago.

What I mean please show me the manual way how 67.8 is calculated such as ? x ? + ? = 67.8. The same for 23.7.

Customer: replied 4 months ago.
67.8 and 23.7 are current balances taken from pay stub. How does payroll figure these balances? For example say I start the year with 80 hours rolled over from previous year and I take 50 hours vacation time during this year that leaves 30 but I will accrue 37.8 hours 4.6hr per pay period now I have 67.8 hours. 80-50+37.8=67.8 Same goes with personal. I can do another sheet and list the vacation time taken and have that total deducted from the total hours that's no problem. What I want is a automated way to deduct accrued time on a bimonthly basis.
Expert:  bbao replied 4 months ago.

Okay. Thanks. In a meeting. Back to you once available.

Customer: replied 4 months ago.
Is the meeting over?
Expert:  bbao replied 4 months ago.

Yes, the meeting was over. Sorry for the delay.

Okay I still need to clarify further. It seems your current calculations such K5=B5+H5 and H6=B6+H6 are based on B5 and B6, how could you calculate B5 and B6 based back to column K and H again?

Expert:  bbao replied 4 months ago.

Are you still there with me?

Customer: replied 4 months ago.
I'm back, sorry
Customer: replied 4 months ago.
Your looking at year end totals if you add current hours plus future accrued hours you get year end totals
Customer: replied 4 months ago.
Your looking at year end totals, if you add current vac hours(B5) plus future accrued vac hours (H5) you get year end totals, they include what will be accrued by end of year.
Expert:  bbao replied 4 months ago.

> if you add current vac hours(B5) plus future accrued vac hours (H5) you get year end totals

I understand that, and your formula at K5 (=B5+H5) also tells that.

I think your question is how to calculate B5 and B6, right?

Customer: replied 4 months ago.
34;I think your question is how to calculate B5 and B6, right?" Yes, Well what I was hoping to do is update B5 and B6 with accrued bimonthly hours. There are 3 pay periods currently left say when that changes to 2 is there a way to add the accrued time to B5 and B6 automatically updating totals.
Customer: replied 4 months ago.
Currently H7 automatically counts down on a bimonthly basis, and changes H5 totals, is there a way to formula add what is deducted from H5 to B5?
Customer: replied 4 months ago.
Currently H7 automatically counts down on a bimonthly basis, and changes H5 and H6 totals, is there a formula to add what is deducted from H5 and H6 to B5 and B6?
Expert:  bbao replied 4 months ago.

Okay, please simply advise your expression to calculate B5 based on H5?

Something like B5 = ? * H5, or you may use numbers if you have to?

Customer: replied 4 months ago.
H5 formula is =E5*H7 can the same formula contain B5-E5?
Customer: replied 4 months ago.
Correction H5 formula is =E5*H7 can the same formula contain B5+E5?
Expert:  bbao replied 4 months ago.

You mean new B5 = old B5 + E5?

Customer: replied 4 months ago.
How can I add 4.6hr to B5 on a bimonthly basis, when H7 formula updates bimonthly to lower number.
Expert:  bbao replied 4 months ago.

Okay, it seems you are not getting what I am asking. I opt out and let other experts help you further.

Customer: replied 4 months ago.
H7 formula is =INT((DATE(YEAR(E2),12,31)-E2)/13) can an expression be added that adds 4.6 to cell B5 at the same time.

Related Microsoft Office Questions