Excel formulas - Can the following be done? (OT) - The Book-keepers Forum (UK)

Leger · Master Book-keeper

Excel formulas - Can the following be done? (OT)

VinceH · Expert

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.

Leger · Master Book-keeper

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?

VinceH · Expert

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.

Leger · Master Book-keeper

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.

VinceH · Expert

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.

Leger · Master Book-keeper

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.

Shamus · Forum Moderator & Expert

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.

Leger · Master Book-keeper

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.

VinceH · Expert

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).

Leger · Master Book-keeper

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.

Cheshire · Master Book-keeper

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!!!

Leger · Master Book-keeper

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.

Artois · Guru

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?

Cheshire · Master Book-keeper

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!' wink biggrin biggrin evileye

VinceH · Expert

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."

Cheshire · Master Book-keeper

biggrin biggrin biggrin your response.

But the posters who do this are getting bloody tedious.