+ Reply to Thread
Results 1 to 25 of 25

Calculation of Dates

  1. #1
    Forum Contributor leo73pk's Avatar
    Join Date
    09-28-2006
    Location
    UAE
    MS-Off Ver
    2016, Microsoft 365
    Posts
    294

    Calculation of Dates

    I have to prepare fortnightly expense statement which is updated on daily basis. Sample workbook is enclosed.

    I just laid the data in Cells C5:C6, while these are calculated on parameter in Cell c3 where 1 represent 1st fortnight and 2 represent 2nd fortnight. (in the attached sample workbook)

    I look forward to a formula or formulas that automatically update headings in Cell C5 & C6. For Sample purpose, I assume that report for 1st fortnight is prepared on Jan 13, 2017 while for 2nd fortnight report is prepared on Jan 22, 2017. While my requirement is that these dates are calculated automatically either by Today() formula or any other better option.
    Attached Files Attached Files
    Best Regards,
    Hamza


    It is the greatest of all mistakes to do nothing because you can only do little - do what you can.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Calculation of Dates

    It's not clear to me what you're getting at. Neither do i understand whether the dates in column H are relevant and if so why they don't seem consistent

    Would you add other sheets and manually enter what you expect to see for fortnight 2,3,..etc

    Rather than the month abbreviation, year number and seemingly inconsistent dates in column H it should be possible to drive all the reported dates from a single base date. But we need to understand what you expect to see for other months.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Contributor leo73pk's Avatar
    Join Date
    09-28-2006
    Location
    UAE
    MS-Off Ver
    2016, Microsoft 365
    Posts
    294

    Re: Calculation of Dates

    Dear Richard,

    Thanks for your feedback. let me rephrase my requirement for your understanding.

    My requirement is simply to updated the headings automatically in cells C5:C6 based on current date.

    Lets assume We prepare report today on Jan 18, 2017 which falls in the second fortnight of January 2017. I'll manually insert 2 Cell C3 (to reflect its 2nd fortnight),
    Now I want following;

    1: Heading appear in Cell C5 as "Actual expense value during Jan 16-17, 2017" and
    2. Heading appear in Cell C6 as "Planned expense value during Jan 18-31, 2017".

    The objective is by inserting 1 for First fortnight and 2 for Second fortnight in Cell C3, headings in Cell C5 & C6 are updated automatically.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Calculation of Dates

    What is wrong with the formulae you have already constructed inside the box?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Forum Contributor leo73pk's Avatar
    Join Date
    09-28-2006
    Location
    UAE
    MS-Off Ver
    2016, Microsoft 365
    Posts
    294

    Re: Calculation of Dates

    Dear Ali,

    In the current formula's I need to do manually change the dates regularly to get the desired output in headings. which i would like to avoid.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Calculation of Dates

    OK - so please create a mock-up of what you would like. Do not include anything that you are currently using to 'fudge' it. Explain clearly the logic behind the dates you wish to show.

  7. #7
    Forum Contributor leo73pk's Avatar
    Join Date
    09-28-2006
    Location
    UAE
    MS-Off Ver
    2016, Microsoft 365
    Posts
    294

    Re: Calculation of Dates

    Ali,

    The rationale and requirement is as follows;

    My requirement is simply to updated the headings automatically in cells C5:C6 based on current date.

    Lets assume We prepare report today on Jan 19, 2017 which falls in the second fortnight of January 2017. I'll manually insert 2 Cell C3 (to reflect its 2nd fortnight),
    Now I want following;

    1: Heading appear in Cell C5 as "Actual expense value during Jan 16-18, 2017" and
    2. Heading appear in Cell C6 as "Planned expense value during Jan 19-31, 2017".

    The objective is by inserting 1 for First fortnight and 2 for Second fortnight in Cell C3, headings in Cell C5 & C6 are updated automatically.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Calculation of Dates

    I understand this, but you are failing to answer the key question: are you expecting Excel to work out the dates for you, or are you going to have a lookup table somewhere on the sheet?

  9. #9
    Forum Contributor leo73pk's Avatar
    Join Date
    09-28-2006
    Location
    UAE
    MS-Off Ver
    2016, Microsoft 365
    Posts
    294

    Re: Calculation of Dates

    Ali,

    The only manual work i would like is to define 1 or 2 for the Fortnight in Cell C3, Rest of the works to be done automatically by excel by updating the headings in Cells C5:C6 based on the Current date and respective fortnight defined in cell C3.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Calculation of Dates

    Thanks for the clarification. Now please explain this:

    1: Heading appear in Cell C5 as "Actual expense value during Jan 16-18, 2017" and
    2. Heading appear in Cell C6 as "Planned expense value during Jan 19-31, 2017".
    How does number one fit in with the functioanlity you have requested? Why is it not Jan 1-18?

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Calculation of Dates

    Thanks for the clarification. Now please explain this:

    1: Heading appear in Cell C5 as "Actual expense value during Jan 16-18, 2017" and
    2. Heading appear in Cell C6 as "Planned expense value during Jan 19-31, 2017".
    How does number one fit in with the functionality you have requested? Why is it not Jan 1-18?

  12. #12
    Forum Contributor leo73pk's Avatar
    Join Date
    09-28-2006
    Location
    UAE
    MS-Off Ver
    2016, Microsoft 365
    Posts
    294

    Re: Calculation of Dates

    Because on Jan 19, 2017, we are in second fortnight and want to review Actual expense value during Jan 16-18, 2017. (c5) and Planned expense value during Jan 19-31, 2017 (C6)

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Calculation of Dates

    I'm sorry - I don't get why planned expense would be a 14-day period and actual only two. Maybe someone else can help you with this. I just don't feel confident that I can predict what you expect to happen in subsequent periods. Good luck with it!

  14. #14
    Forum Contributor leo73pk's Avatar
    Join Date
    09-28-2006
    Location
    UAE
    MS-Off Ver
    2016, Microsoft 365
    Posts
    294

    Re: Calculation of Dates

    Its simple because we were reviewing our report on 19th of January 2017 which is 4th day of Second Fortnight so we review expenses during first three (3) days of the second fortnight.

  15. #15
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Calculation of Dates

    leo73pk,

    While I have had difficulty understanding what you have posted I took a different problem solving approach. I've attempted to follow the patterns of the formulas in the upload. The rest I just guessed.

    I put this formula in E11:E12 that 'shadows' your formula in E14:E15 so I could check my work.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Then to generate the calculation based upon TODAY() in C1 I put
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    I then experimented with values in C2 to test the following formula in C3 which must be array entered. It returns the fortnight number. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Has this helped?
    Attached Files Attached Files
    Dave

  16. #16
    Forum Contributor leo73pk's Avatar
    Join Date
    09-28-2006
    Location
    UAE
    MS-Off Ver
    2016, Microsoft 365
    Posts
    294

    Re: Calculation of Dates

    Dear Flame,
    Thanks for your reply and efforts to guide me. I think you are right my query gets quite confusing now after several posts perhaps im unable to convey my problem clearly let me redefine my requirements in a fresh sheet, just ignore previous posts
    I have current date in Cell E3 based on a function TODAY() in Cell E3.
    Now based on the date in Cell E3, I need two headings in cell D6 and D7 as follows;
    D6: Actual expense during Jan 16-20, 2017
    D7: Planned expenses for the period Jan 21-31, 2017l.
    (Rationale for updating the heading in Cell D6: Based on the 1st day of the fortnight wherein the date in cell E3 falls, till the date before the date in Cell E3 i.e. TODAY()-1).
    (Rationale for updating the heading in Cell D7: Based on the Date in cell E3, till the last date of the fortnight wherein the date in Cell E3 falls
    I just want these headings to be updated on any given date in Cell E3
    Attached Files Attached Files
    Last edited by leo73pk; 01-21-2017 at 02:20 PM.

  17. #17
    Forum Contributor leo73pk's Avatar
    Join Date
    09-28-2006
    Location
    UAE
    MS-Off Ver
    2016, Microsoft 365
    Posts
    294

    Re: Calculation of Dates

    Simply ignore the previous posts in this thread except the last one with the attachment of Calculation of Dates and description of the problem.

  18. #18
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Calculation of Dates

    I completely understand what you are trying to do, but I don't understand the following:

    D6: Actual expense during Jan 16-20, 2017
    What is the rationale for these specific dates? What would they be if D7 were showing the planned expense period as 01-20 Jan?
    D7: Planned expenses for the period Jan 21-31, 2017.
    What about the other months? Will the second fortnight always begin on the 21st?

    The reason we are unable to help is that you keep saying what you want to achieve, but never answer the questions asked.

  19. #19
    Forum Contributor leo73pk's Avatar
    Join Date
    09-28-2006
    Location
    UAE
    MS-Off Ver
    2016, Microsoft 365
    Posts
    294

    Re: Calculation of Dates

    Ali,

    Although in my last post I have clearly elaborated my requirement. Let me respond to your queries again as follows;

    First of all understand the fact we treat every month in two fortnights as the budgets are available on fortnightly basis. Based on this, on any specific date we review the summary of expenses i.e. on 23 Jan 2017 (which is in 2nd fortnight) we review summary of Actual expenses during Jan 16-22, 2017 (that's how my heading to be appeared in D6), Similarly from 23 Jan 2017 till the end of fortnight i.e 31st, we will see the planned expenses for the period Jan 23-31, 2017 (that's how my heading to be appeared in D7) and similarly that should work for any specific date of any particular month.

  20. #20
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Calculation of Dates

    I give up. You still haven't answered the questions I asked. Good luck with this: I'm afraid I've given up trying to extract the information I need to help you.

  21. #21
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Calculation of Dates

    I'm afraid I must give up as well. I am more lost than ever. Wish you the best. Sorry.

  22. #22
    Forum Contributor leo73pk's Avatar
    Join Date
    09-28-2006
    Location
    UAE
    MS-Off Ver
    2016, Microsoft 365
    Posts
    294

    Re: Calculation of Dates

    I'm enclosing a sample sheet, where in I give the working block as well thru which i'm able to update the headings in Cells D5 and D6 based on the current date in E3.

    I hope now it gives you clear understanding of the issue. In view of foregoing, I would like to request the members of this forum to suggest any better and more effective solution.
    Attached Files Attached Files

  23. #23
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Calculation of Dates

    Finally something we can work with - thank you!

    In D6:

    ="Actual Expenses during "&CONCATENATE(TEXT(MONTH(TODAY()),"mmm")," ",IF(DAY(TODAY())>15,16,1),"-",DAY(TODAY()-1),", ",YEAR(TODAY()))

    In D7:

    ="Planned expenses for the period "&CONCATENATE(TEXT(MONTH(TODAY()),"mmm")," ",DAY(TODAY()),"-",IF(DAY(TODAY())>15,DAY(EOMONTH(TODAY(),0)),15),", ",YEAR(TODAY()))

    If this isn't quite what you want, it should give you enough to be able to adapt it. All you had to do was put together the formulae you already had.

  24. #24
    Forum Contributor leo73pk's Avatar
    Join Date
    09-28-2006
    Location
    UAE
    MS-Off Ver
    2016, Microsoft 365
    Posts
    294

    Re: Calculation of Dates

    Thanks Ali,

    Eventually you solve my problem. Thanks a lot

  25. #25
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Calculation of Dates

    Yes, but it was like getting a blood out of a stone. I'm glad we got there, but if you had given us the necessary information when we first asked for it, we could have solved it days ago.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Calculation of dates, return blank when one of the dates is not populated
    By brian_2me in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-15-2014, 10:59 AM
  2. [SOLVED] Calculation ages (No Dates)
    By Italian Job in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 01-02-2014, 08:05 AM
  3. [SOLVED] excel formula to help my calculation from min dates of 3 dates cell ranges
    By breadwinner in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-04-2013, 10:32 AM
  4. Excel 2007 : Calculation with dates
    By doogiejeh in forum Excel General
    Replies: 2
    Last Post: 02-15-2011, 05:46 PM
  5. [SOLVED] Calculation with dates:several payments
    By Vanna in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-04-2006, 02:35 AM
  6. Calculation between two dates
    By Rameris in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 01-28-2006, 08:15 PM
  7. [SOLVED] calculation sundays between two dates
    By jeff thinkin in forum Excel General
    Replies: 3
    Last Post: 09-14-2005, 10:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1