Show the day in which the 1st of the month falls

Question

Level 1

8 points

Show the day in which the 1st of the month falls

Hi all, I’ve been trying this for some time now and I’m still scratching my head!

I track my monthly pay using numbers. I want to automatically insert the date for the start day of each week.

Im currently inputting the dates myself but i want to pass this out to colleagues who have very little knowledge of how to use a spreadsheet!

So i want to put the first date as the 1st of march for the financial year that were in, currently 2018-19. This happens to fall on a Thursday. This is fine but I then want the start date of each subsequent week within that month. As you can see, in my screen shot below, the next Monday after the first should be 5th march then 12, 19, 26. The next date I want to show is 1st April followed by 2, 9,16, 23 & 30.

Is there any formula to work this out? please help?!?!?

iPad Air Wi-Fi + Cellular, iOS 11.1, iPad Air 2

Posted on Mar 4, 2018 1:48 AM

Reply

Answer 1

Yellowbox

Level 7

20,389 points

Mar 4, 2018 2:47 AM in response to tonyRDFC

Hi Tony,

To partly answer your question, the EDATE function:

The EDATE function returns a date/time value that is some number of months before or after a given date.

EDATE(start-date, month-offset)

start-date: The starting date. start-dateis a date/time value (the time portion is ignored) or date string.

month-offset: The number of months before or after the starting date. month-offset is a number value. A negative month-offset is used to specify a number of months before the starting date and a positive month-offset is used to specify a number of months after the starting date.

Enter the first date in A2

Formula in A3 (and Fill Down)

=EDATE(A2,1)

I am not sure what you want to do after that. Are you looking for each Monday in a month?

Regards,

Ian.

Reply

Answer 2

SGIII

Level 8

35,336 points

Mar 5, 2018 2:07 PM in response to tonyRDFC

Hi Tony,

You can try something like this:

A2 is manually input, the start of the fiscal year.

The formula in B2, filled down, is:

=A2+(7−WEEKDAY(A2,2))

The formula in C2, filled down, is:

=EOMONTH(A2,0)

The formula in D2, filled down, is:

=MIN(B2:C2)

The formula in E2, filled down, is:

=D2−A2+"1d"

The formula in F2, filled down, is:

=MONTHNAME(MONTH(A2))

The formula in A2, filled down, is:

=D2+1

Hide columns B and C. Or you can eliminate them by combining the formulas in B and C into having one big formula.

In C2, filled down:

=MIN(A2+(7−WEEKDAY(A2,2)),EOMONTH(A2,0))

Substitute ; for , in the formulas if your region uses , as a decimal separator.

If you're passing this off to colleagues you could derive the dates and then "remove" the formulas via command-c to copy followed by Edit > Paste Formula Results.

SG

Reply

Answer 3

SGIII

Level 8

35,336 points

Mar 5, 2018 2:08 PM in response to tonyRDFC

Hi Tony,

In your first post you wrote:

5th march then 12, 19, 26. The next date I want to show is 1st April followed by 2, 9,16, 23 & 30.

That is precisely what my example shows. If that's what you want (most workweeks starting on a Monday (unless there is a month-end) then just change the Data Format to a UK format.

But in your latest post you write:

Wk 01 = 01/03/2018 (UK Calendar!)

wk 02 = 02/03/2018

wk 03 = 09/03/2018

wk 04 = 16/03/2018

wk 05 = 23/03/2018

wk 06 = 30/03/2018

wk 07 = 01/04/2018

wk 08 = 06/04/2018

That's something different entirely, with March workweeks starting on Fridays! Is that really what you want? If so, that would imply that your UK calendar, rather than just UK date formatting, is something really different from what I've ever seen!

I think you can just change the date formatting. The table then looks like this:

Note that the YYYY-MM-DD format is a choice both in the US and UK Regions. So trying my example as shown originally will work there too. Have you actually tried it?

SG

Reply

Answer 4

tonyRDFC Author

Level 1

8 points

Mar 5, 2018 6:23 AM in response to SGIII

If I was your K9 friend now I’d been issuing a big slavery Kiss to you now!

It has worked but in A3 i used =D2+1 then hidden cells B:F.

Your a star. I can only apologise as I’ve only just tried the formulas now as I’ve been at work now for the last 15 days!! And haven’t had a spare min.

Id buy you a pint if we were in a bar now or a coffee if we were in a cafe!

THanks for your time.

Reply

Answer 5

tonyRDFC Author

Level 1

8 points

Mar 4, 2018 7:07 AM in response to Yellowbox

Wow thanks for the fast reply.

Basicily the spreadsheet is for working out all my overtime for a given month.

so, for argument the month starts on a Thursday, so wk 1 will be overtime for Thursday through to sunday.

wk 2 = mon - sun

wk 3 = mon - sun

wk 4 = mon - sun

wk 5 = mon - wed

then i need the date for the next row to be the 1st of the next month, in the above example Thursday 1st, and so on. I have it running from march to march.

Reply

Answer 6

tonyRDFC Author

Level 1

8 points

Mar 4, 2018 2:54 PM in response to SGIII

I’m really greatful of you for taking the time to post these responses.

But for the Above examples I’d need the results to read

Wk 01 = 01/03/2018 (UK Calendar!)

wk 02 = 02/03/2018

wk 03 = 09/03/2018

wk 04 = 16/03/2018

wk 05 = 23/03/2018

wk 06 = 30/03/2018

wk 07 = 01/04/2018

wk 08 = 06/04/2018

etc. (Wk numbers doesn’t mater i used these as an example as my wage year stars in march)

Kind Regards

Tony

Reply

Answer 7

tonyRDFC Author

Level 1

8 points

Mar 5, 2018 12:34 AM in response to SGIII

Im sorry for being confusing I just put random dates in the first post! My apologies.

All I’m trying to acheive is the following: My overtime is paid a month in arrear. The month runs from 1st day to the last day. I have to send a weekly Timesheets in to my office. Each week i input the OT data in to the spreadsheet. So if the month starts on a Friday then any OT earned on the Friday thru to Sunday needs to be inputted as wk1 of that month.

Reply

Answer 8

SGIII

Level 8

35,336 points

Mar 5, 2018 3:08 AM in response to tonyRDFC

tonyRDFC wrote:

Each week i input the OT data in to the spreadsheet. So if the month starts on a Friday then any OT earned on the Friday thru to Sunday needs to be inputted as wk1 of that month.

Hi Tony,

I'm reasonably confident it does what you are describing (though I'm less sure now of what you want because your first post and your second post contradict each other; your second post has workweeks in March going from Friday to Thursday while your first post is not "random" dates but has workdays going from Monday to Sunday as one would expect).

In my suggested solution, if the month starts on a Thursday then the first week is from Thursday to Sunday. If the month starts on a Friday then the first week will be from Friday to Sunday. If it starts on a Monday then it will be from Monday to Sunday, etc. And the last week of the month will end on the last day of that month if it falls earlier than the following Sunday.

Have you actually tried the solution I've suggested?

SG.

Reply

Answer 9

SGIII

Level 8

35,336 points

Mar 5, 2018 6:30 AM in response to tonyRDFC

Hi Tony,

Glad it worked out. You credited yourself as "solving" your problem so your message will now appear at the top as having solved the problem and that is what future readers will see as the solution. If you found any other posts here helpful you can, if you want, mark up to two of them as "Helpful" and they will appear at the top too for future readers.

SG

Reply