The Book-keepers Forum (BKF)

Post Info TOPIC: Excel formulas - Can the following be done? (OT)


Master Book-keeper

Status: Offline
Posts: 2924
Date:
Excel formulas - Can the following be done? (OT)


Nothing to do with work, but I'm just wondering if the following can be done.

row 1  date

row 2  same date

row 3  increase by 1 day and repeat.

Not essential but on 11th row increase date by 3

eg  row 1 3/6/19   row 10  7/6/19  row 11 10/6/19

and continue so you have 10 rows for each week Mon - Fri

 

also as a separate exercise 

row 1   say 4/6/19 

row 2  6/6/19  

row 3  increase 7 days from row 1

row 4  increase 7 days from row 2

etc

 

 



__________________

John 

 

 

 Any advice given is for general guidance and professional advice should be sought applicable to your circumstances.



Expert

Status: Offline
Posts: 1675
Date:

Not being an Excel user, I don't know if there's a built in way of automagically creating sequenced weekday rows*, but if not it should be possible to do the first block manually, then copy the block down as often as needed.

Here's how I'd probably do it in LibreOffice Calc, using today's date as the start point (it's Monday, so that's convenient) and putting the dates in the first column:

In A1, enter the starting date: 3/6/2019
In A2, enter a forumula: =A1
In A3 enter a forumula: =A2+1

Now copy A2 and A3 to A3 to A10

In A11 enter a formula: = A10+3

Now copy A2 to A10 into A12 to A20

That's your first two weeks. From this point on you can copy the Monday to Friday block from A11 to A20 into the empty space below.

(Or copy the rows rather than the cells as necessary if and when you ever use a spreadsheet set up like that).

You can adopt a similar approach with your second question - it's just a matter of adjusting the formula as needed to suit what you want. (And knowing if in Excel adding 1 to a date increases the date by one day).

* There will almost certainly be functions to calculate the day number within a week, the actual day from the date, and so on - but unless there's one more useful than that, to use those to do what you want would probably mean more complicated formula than the above.

__________________

Vince M Hudd - Soft Rock Software

(I only came here looking for fellow apiarists...)



Master Book-keeper

Status: Offline
Posts: 2924
Date:

Thanks Vince, that's further than I got and will cut down the time considerably.  I know =cell above +1 increases the date consequently when you drag the bottom right hand corner and use that successfully, but I was rather stumped with there being 2 on a monday etc. I'll be doing it in Libre anyway as I don't have excel at home.

How are you, have things improved on the work front?

 



__________________

John 

 

 

 Any advice given is for general guidance and professional advice should be sought applicable to your circumstances.



Expert

Status: Offline
Posts: 1675
Date:

Slightly improved. Cash is still very tight, so I have to be ultra careful on what I spend - but as long as I *AM* ultra careful, I can get by. I am still looking at job adverts and applying, though. Although a 'proper job' would involve something more 9-5 (rather than my more usual 10:30 ish until whenever*), the truth is I'd be much better off - even with something paying less per hour than I charge.

Things did get slightly worse for a moment, though - about a month back, I commented to someone that the worst thing that could happen now would be a significant problem with the car. A couple of days later, the day after I returned from a trip up North, I was on my way to a client's office when the car broke down. Badly - an estimated £1500-£2000 repair bill for a car that's worth maybe a few hundred if I'm lucky. (And for which I spent a grand having the clutch replaced last year). :(

A month of using the bus later, I now have a different car on the road - buying that cost less than the repair on the old one.

* In fact, my more usual 10:30ish start is now a more usual 9:30ish start. This is a result of catching the bus - I had to be ready to leave home an hour earlier than I would by car and I've become used to leaving that much earlier, so now I do even with a car, and therefore arrive at places that much earlier. So the shift to a 9:00am start now probably wouldn't be that painful.

__________________

Vince M Hudd - Soft Rock Software

(I only came here looking for fellow apiarists...)



Master Book-keeper

Status: Offline
Posts: 2924
Date:

Ouch with the car, there's nowt worse than something compounding an already serious situation.  Glad things have improved slightly and i hope they improve even more for you.

I'm a bit like yourself, as I prefer to get up around 9 ish and potter for a couple of hours at home. I'm a late person though so I'm rarely in bed before 12 - 1 am.

 



__________________

John 

 

 

 Any advice given is for general guidance and professional advice should be sought applicable to your circumstances.



Expert

Status: Offline
Posts: 1675
Date:

Yeah, very similar - or at least I used to be!

Typically, I've always had my alarm set for 7:30, but then hit snooze a few times* and tended to actually get up around 8:15. I'd then do my morning things, and potter around on a computer until about 10, which is when I'd leave for wherever, to arrive for 10:30ish. (Or if I'm working from home, potter around until 10:30).

To aim for a 10:30 start when out at clients, I had to leave home around 9 when using the bus - so I shifted the alarm forward slightly to 7:00am, and those few uses of the snooze button meant getting up around 7:45. Combine that with a little less messing around on the computer to leave around 9, and that's the routine I've now fallen into.

At the other end of the day, what I used to do was go to bed around 10:30, and watch TV in bed until around 12:30-1:00ish - and then I'd catch up on a few feeds on my phone, so another 30 minutes to an hour before I "really" turn in. Now I'm watching my TV recordings *before* going to bed at around 11ish, and doing that final bit of phone reading.

So it is possible to change the daily wake/sleep cycle. :)

(Although when it turns colder again, I might be less keen to throw off the duvet!)

* I actually use two alarms. The clock radio, which comes on (to Classic FM) at a low volume. I don't snooze that - I let it play, to help me gently wake up. My phone alarm then goes off fifteen minutes later, and that's a more sudden noise, and that gets a couple of fifteen minute snoozes, while I think about considering the possibility of maybe perhaps getting up.






__________________

Vince M Hudd - Soft Rock Software

(I only came here looking for fellow apiarists...)



Master Book-keeper

Status: Offline
Posts: 2924
Date:

My phone alarm is set for 8 am.  At one time I could leap out of bed at that point but I now find it takes me half hour to an hour to get up, depending how many times I hit snooze!!

No TV in bed and I record everything then have a couple of hours catching up on them roughly between 10 and 12 although occasionally I extend that to 1 am. (On rare occasions I'll just sit here til about 4 in the morning even though I know it's going to throw the following day out) Just watched the first two series of Line of Duty and that had me glued (still got the last 3 series to watch) I then read for roughly half an hour before getting my head down.



__________________

John 

 

 

 Any advice given is for general guidance and professional advice should be sought applicable to your circumstances.



Forum Moderator & Expert

Status: Offline
Posts: 11622
Date:

Leger wrote:

Nothing to do with work, but I'm just wondering if the following can be done.

row 1  date

row 2  same date

row 3  increase by 1 day and repeat.

Not essential but on 11th row increase date by 3

eg  row 1 3/6/19   row 10  7/6/19  row 11 10/6/19

and continue so you have 10 rows for each week Mon - Fri

 

also as a separate exercise 

row 1   say 4/6/19 

row 2  6/6/19  

row 3  increase 7 days from row 1

row 4  increase 7 days from row 2

etc

 

Sorry, late to the party.

ok, you need to be thinking about a diffferent formulae here. The scenario you descrribe you are looking to only process workdays so the formulae is :

=workday(a1,1)

that will add one workday to the date in cell a1.

With your scenario you would put the dates you want in A1 and A2.

Put the formula in cell A3

Drag the formula down one cell.

Grab cells A3 and A4 together and drag down a far as you like.

Ta dah....

To check it, simly put in cell B12 this formula then double click the drag button which will repeat the formula into eaxch cell where there is a value in a cell next to it.

=text(a1,"ddd")

The only days shown will be mon-fri and they will be in batches of two

HTH,

Shaun.

p.s. there is an option with workday to also exclude holidays but thats a slightly more advanced topic.



__________________

Shaun

Responses are not meant as a substitute for professional advice. Answers are intended as outline only the advice of a qualified professional with access to all relevant information should be sought before acting on any response given.



Master Book-keeper

Status: Offline
Posts: 2924
Date:

Cheers Shaun, that worked a treat in Excel and hopefully it'll work in Libre as well, will try it when I get back on my home comp.

 



__________________

John 

 

 

 Any advice given is for general guidance and professional advice should be sought applicable to your circumstances.



Expert

Status: Offline
Posts: 1675
Date:

Just took a look myself and, yes, WORKDAY() is a function in LibreOffice Calc. Perhaps if I'd run the program yesterday I might have noticed it, rather than just coming up with a way to do it (which also works).



__________________

Vince M Hudd - Soft Rock Software

(I only came here looking for fellow apiarists...)



Master Book-keeper

Status: Offline
Posts: 2924
Date:

Whilst workday is neat, the solution you suggested works equally well and it doesn't take long to set it up.  Many thanks to both of you. 



__________________

John 

 

 

 Any advice given is for general guidance and professional advice should be sought applicable to your circumstances.



Master Book-keeper

Status: Offline
Posts: 7535
Date:

Hi Vince
Good to hear life has improved albeit only slightly and out about the car!!!!!! Just been chatting to someone else about the fact it would probably be cheaper to buy one rather than repair their existing one, but sometimes it hard to part company!

I certainly dont envy you having to get out of the house that early! Round here if you leave before 9.30 you just sit in traffic, in fact to be honest Ive been at a clients for 7.15 the last few days just to avoid that wasted sitting in traffic time, but getting up so early is killing me! Not helped by the fact I dont like to go to bed early of an evening because I feel robbed of the evening! Bonkers! Must admit though Ive knocked the 'snoozing' on the head and feel better for it. 7 minutes of drifting back to sleep, 2 minutes and deep deep sleep and the damned alarm going off again was doing my head in.

Hope things keep improving and quickly!!!

__________________

Joanne  

Fallows Hall Ltd 

Winner - Bookkeeper of the Year 2015, 2016 and 2017

Thoughts are my own/not to be regarded as official advice,which should be sought from a suitably qualified Accountant.

 



Master Book-keeper

Status: Offline
Posts: 2924
Date:

Hi Joanne

What's your secret lol - there was a time when I could wake up in a morning and get straight out of bed.  Not any more - even if I need the loo when I wake up I have to go back to bed for a bit.

I know what you mean about feeling robbed of an evening, that's definitely me as well.



__________________

John 

 

 

 Any advice given is for general guidance and professional advice should be sought applicable to your circumstances.



Guru

Status: Offline
Posts: 816
Date:

 

I am the opposite, I am very much an early morning person and always have been, I will be up between 6.00 and 6.30 every day including weekends in fact 7.00 for me is a lay in, I prefer mornings as I seem to get more done, however I am usually in bed by about 10.30 to 11.00 of a night so I suppose it is all relative.

What happened to all the newbies who joined a few weeks ago, the forum seemed to get busier and now they all seem to have disappeared again, is it really that scary on here? 

 



__________________

Doug

These are only my opinions of how I see things and therefore should not be taken as advice



Master Book-keeper

Status: Offline
Posts: 7535
Date:

No idea Doug - some weird stuff going on! I would say its cos I said hello, but they had all logged off and hadnt been back on since before I said it!!!

I think we maybe should answer all new ones and returners who are looking forward to joining in with a standard ' your comment will be attended to in 48 hours but only if you log back on in that time!' winkbiggrinbiggrinevileye 



__________________

Joanne  

Fallows Hall Ltd 

Winner - Bookkeeper of the Year 2015, 2016 and 2017

Thoughts are my own/not to be regarded as official advice,which should be sought from a suitably qualified Accountant.

 



Expert

Status: Offline
Posts: 1675
Date:

Hmm. I am reminded of one of the silly answering phone messages I used when I first had a phone: Something like "Please leave your message after the beep, and I'll ignore it as soon as possible."

__________________

Vince M Hudd - Soft Rock Software

(I only came here looking for fellow apiarists...)



Master Book-keeper

Status: Offline
Posts: 7535
Date:

biggrinbiggrinbiggrin your response.

 

But the posters who do this are getting bloody tedious. 

 



__________________

Joanne  

Fallows Hall Ltd 

Winner - Bookkeeper of the Year 2015, 2016 and 2017

Thoughts are my own/not to be regarded as official advice,which should be sought from a suitably qualified Accountant.

 

Page 1 of 1  sorted by
 
Quick Reply

Please log in to post quick replies.

Tweet this page Post to Digg Post to Del.icio.us
Members Login
Username 
 
Password 
    Remember Me  

©2007-2019 The Book-keepers Forum (BKF). All Rights Reserved. The Book-keepers Forum (BKF) is a trading division of Bookcert Ltd. Registered in England Company Number 05782923. 2 Laurel House, 1 Station Rd, Worle, Weston-super-Mare, North Somerset, BS22 6AR, United Kingdom. The Book-keepers Forum and BKF are trademarks of Bookcert Ltd. This forum is a discussion forum only. There will usually be more than one opinion to any question and any posting should not be viewed as a definitive solution. No responsibility for loss occasioned to any person acting or refraining from action as a result of any posting on this site is accepted by the contributors or The Book-keepers Forum. In all cases, appropriate professional advice should be sought before making a decision. We reserve the right to remove any postings which are offensive, libellous, self-promoting or engaged in covert marketing. We will not notify users of removals. The views expressed in the forum posts are those of the individual and do not necessary reflect or agree with those of The Book-keepers Forum. Any offensive or unsuitable posts will be removed by the moderators. Any reader of this forum can request for a post to be looked into by sending an email to: info@bookcert.co.uk.

Privacy & Cookie Policy  About