Hi,
I'm trying to make a timesheet template where the dates are filled in based on the year and month, however the cut off dates for time period run into the next month. I have it set up with end of month but can't figure out how to formulate start of next month.
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Automatic Dates
0
Discussion
Hi again RSWM
Looking at your file (on my mobile phone) I guess you're referring to the formula in D5 (and right) on the Automated Timessheet worksheet. I'm not clear what you're trying to do - can you expand your question and give sample dates for when "the cut off dates for time period run into the next month"?
Looking at your file (on my mobile phone) I guess you're referring to the formula in D5 (and right) on the Automated Timessheet worksheet. I'm not clear what you're trying to do - can you expand your question and give sample dates for when "the cut off dates for time period run into the next month"?
John_Ru (rep: 6417) Sep 26, '24 at 12:31 pm
Yes D5 and right. Cut off date would be the 5 of the next month. Dates run from 1st-20th and 21st - 5th.
RWSM (rep: 14) Sep 26, '24 at 1:10 pm
Thanks. Might have time to respond tomorrow but which version of Excel are you using? (It helps if you can add that as the MS Office Version to your Forum Profile)
John_Ru (rep: 6417) Sep 26, '24 at 3:47 pm
That would be great, thank you.
Using 365 for business
Using 365 for business
RWSM (rep: 14) Sep 26, '24 at 3:53 pm
Ranae- you said "Dates run from 1st-20th and 21st - 5th" (and I solved that) but did you mean to say "Dates run from 6th-20th and 21st - 5th", perhaps to align with your tax year start date? (Otherwise you'd have a gap like 6th - 30th in the next month).
John_Ru (rep: 6417) Sep 27, '24 at 9:30 am
OMG, yes that's what I meant, sorry about that.
RWSM (rep: 14) Sep 27, '24 at 10:24 am
Also, how would this work when it come to a new year? December 21-January 5
RWSM (rep: 14) Sep 27, '24 at 10:49 am
Yes! Just posted an answer but tested it for month 12, even period.
John_Ru (rep: 6417) Sep 27, '24 at 12:45 pm
Just corrected some typos in my Answer bit have to dash!
John_Ru (rep: 6417) Sep 27, '24 at 12:51 pm
This works great except the periods are 6 - 20 and 21 - 5 which was easy to change. But the month of February brings errors into the end of month code that I can't quite fgure out
RWSM (rep: 14) Sep 27, '24 at 1:49 pm
Argh! I didn't test for February. Will try to do so over the weekend, if I get chance.
John_Ru (rep: 6417) Sep 27, '24 at 2:01 pm
RWSM- please see my revised Answer and new file.
John_Ru (rep: 6417) Sep 27, '24 at 4:16 pm
Thank you!! Works great.
RWSM (rep: 14) Sep 29, '24 at 7:05 pm
Answers
0
Selected Answer
RWSM
Based on your clarification that period dates "run from 6th-20th and 21st - 5th" of each month, I've assumed that you have two period numbers, one odd and the other even,
Revision #1, 27 September 2024 (Revises equations to correct start/stop dates and to prevent errors in February).
In sheet "Automated Time sheet" of the revised file attached, cell D5 (font currently yellow to highlight) has this formula:
=IF(ISODD(V7),DATE(V3,V4,6),DATE(V3,V4,21))so if V7 (filled yellow) is odd (e.g. 5), then D5 contains the date of the 6th of the month/year indicated by the cells above V7 (otherwise it's the 21st). It appears as a number since the formatting is Custom "d" to give the day of the month.
The cell to the right, E5, has the same formatting but is:
=D5 + 1so the day after (and that's copied as far as Q5).
In R5, the end of the period may have been reached so the formula must chnage to test for that (and show nothing if the period is over). The formula in R5 is a Nested If staement:
=IF(Q5<>"",IF(DAY($D$5)=6,IF(DAY(Q5+1)<21,Q5+1,""),IF(Q5+1<EOMONTH($D$5,0)+5,Q5+1,"")),"")where the outer IF tests to see if the previous cell was blank (in which case show another blank).. In the inner If, the True portion finds the end of the period if it's a period starting 6th while the FALSE outcome (in bold) checks for the end of a period which starts on 21st (and spills into the next month). That's copied to cell S5 too.
That means that row 5 could contain a blank at the end so C5 contains this:.
=IF(D5<>"",D5,"")so if D5 isn't a blank, C5 contains the same date (but formatted Custom "ddd" to give the weekday e.g. Sat). That copied as far as S5
If you change V5 to 6, you'll see the period changes to suit.
Note that I changed the heading in row 3 to expand your formula, adding the bit in bold to become:
=TEXT(DATE(V3,V4,1),"[$-US-en]mmmm") &" "&V3 & IF(ISEVEN(V7), "/ " & TEXT(DATE(V3,V4+1,1),"[$-US-en]mmmm"),"")This might give a heading like July 2024/ August if the period starts on the 22nd.
I had to replace your conditional formatting (and won't go into that) but the four rules are all based on the formula:
=IFERROR(IF(WEEKDAY(D$4,2)>5,1,0),0)where the IFERROR prevents problems from a blank in row 4. The vatious shadings apply to different ranges.
I didn't do anything with the dates in the sheets now named "Holidays" but having rows 4 and 5 as dates should make it easier).
I left the sheet showng a February entry (to show a problem you had was fixed).
Hope I got this right. If so and this solves your problem, please be sure to mark this Answer as Selected.