date formulas

  1. S

    Conditional Formating Required for changing Dates based on 2 different cell dates

    Hi, I creating a permit register. End Goal : Have the text in both Cells in "Start Date" and "Finish Date" have the same colour to determine if the Permit is 'Expired', 'Current' or set in the 'future' Column H (Permit Start Date) and Column I (Permit Finish Date). Permit dates do not have...
  2. S

    What is the Substitute for COUNTIFS date based formula ?

    So I have this formula in excel in my raw data =COUNTIFS([Date range],">="&(EOMONTH(Date,-1)+1),[Date range],"<="&EOMONTH(Date,0)) Power BI however doesn't have this function. I tried to use Calculate, but it is still confusing for me to understand some of these formulas. Can you please show me...
  3. S

    Rounding a date to the nearest half year

    Hi, I'm looking to create a formula that will round a date to the nearest half year (either the start of the year or the middle of the year depending upon which is next) i.e if the date was 01/08/2019 the formula would round to 01/01/2020 if the date was 03/04/3021 the formula would round to...
  4. C

    Highlight date

    I have a holiday tracker calendar and it uses macros and formulas to calculate days and dates etc. I have this code...
  5. C

    Generating Month from Date for Fiscal Calendar

    Greetings, <tbody> My data is in the format MM/DD/YYYY and I want to generate the fiscal month and number. Example: Cell contains 7/5/2017 and formula generates 1-Jul. I have tried using the Month and Choose functions. I can get the output to yield the correct month number, but not the...
  6. S

    Direct Debit Fee Completion Date - Month Date Formula relevant to the DD Date i.e. 28th Month

    I would like to return the finish date of a Direct Debit for Customers, when the Direct Debit due date varies within a month. No problems for weekly or fortnightly. However if it will take 48.6 payments to finish the "loan" I would like the finish date to be the 28th (or 15th) of the relevant...
  7. L

    Formula to determine status based on dates in 2 other cells

    Hello - I'm new to Mr. Excel - please forgive me if I do not yet have the forum etiquette down. Let me explain what I do currently have correct.... I have 2 columns (Column N has a "projected meeting date", Column O has an "actual meeting date"). I also have one cell (N12 with the "TODAY"...
  8. L

    TimeSheet date forumla questions

    Thank you in advance to anyone willing to help a beginner out. I have read multiple posts but I'm still stumped. Here it goes: I would like to have three active columns: "Column B" a date in 1st, 2nd, 3rd, etc. format. (Cells B10 through B25) "Column C" a day of the week in Mon., Tues., Wed...
  9. M

    Need help with a formula modifier

    I have a section in this spread sheet that is working really well but now I need to modify it to fit a new requirerment. The first cell returns the number of personnel The second cell returns the number of expired certificates The third cell returns the total number of valid certificates The...
  10. M

    Cash Flow Manager application....Need smart date formulas...Please Help...

    Hi All - Thanks for taking time to view this. I am writing a cash flow manager application based in Excel 2010. My client enters a date in to Cell A1 and I want to be able to forecast cash flows from that date based on historic banking data that I have normalised. The transactions on the...
  11. N

    Using COUNTIF formula

    I have an excel spreadsheet where I need to count the number of occurances where the date is over 3 days from current date. Is this possible? Or, do I have to put a < "current date" , and have to continually change it when I remove/add data? Hope this makes sense! My first time posting on a...
  12. J

    Date formula help please!

    Hi, I wondered if somebody could help me please? I am looking for a formula which returns the date each Monday. So, each Monday the date would change but then stay the same until the following Monday. I have asked before for the same regarding Friday's but I am unable to alter it so it...
  13. M

    Date Functions

    I am using the following formula to calculate workdays over due. G6 has the issue identification date H6 has the formula =workday(g6,5) which is the target date I6 has the issue closed date J6 has the formula =IF(ISBLANK(I6),NETWORKDAYS(H6,TODAY()),NETWORKDAYS(H6,I6)-1) Since I am...
  14. I

    Date functions using if

    I have a couple of questions. I'm trying to create a formula so that I can use a reference cell to adjust another cell. for instance. Say I have three dates 5/10/03, 4/28/05 and 9/8/08. I want other cell to return the dates 5/10/10, 4/28/10 and 9/8/10 where the year is constantly 2010. is...
  15. B

    Convert DD-MM-YYYY hh:mm:ss to DD-MM-YYYY

    Hi, I used to know how to do this but can't for the life of me remember or find out how you do it. Basically i need to Convert DD-MM-YYYY hh:mm:ss to DD-MM-YYYY. I've done the obvious format cells change but this retains the time, not visually but if you were to click on the cell it shows...
  16. K

    How to display list of dates for the year excluding Sundays

    Hello, I would like to use a formula to take the starting date in cell A1 and then have Excel give me a list of all the dates for 2009 excluding Sundays. So A1 would be 1/01/09 Then Excel would list Thursday, Jan 01 Friday, Jan 02 Saturday, Jan 03 Monday, Jan 05 Tuesday, Jan 06 etc.... I've...
  17. D

    If/Then formula using text and dates

    I have three cells I want to use, for this purpose I'll go with A1, B1, and C1. If A1 has the word (SECRET), then I want C1 to add ten years to the date in B1 and if the word in A1 says TOP SECRET, then I want C1 to add five years to the date in B1. If there is nothing in A1, then I want C1 to...
  18. W

    Dates don't format correctly in different computer!

    Hi, I've emailed a workbook through to a new computer (which has the same 2003 Excel edition) and the dates aren't recognized correctly, in my formulas, expressing a '#VALUE!' message, instead! Any way to get round this? I've tried clearing the contents and reformatting the cells affected, but...
  19. S

    Use filename as date

    If the file name of a spreadsheet is 01-10-09.xls, is there a formula to use the file name as the date in any given cell? I tried =CELL("filename") but that gives the whole path, and all I need is the numbers so that the date will show up in this cell in dddd, mmmm dd, yyyy format. Any help is...
  20. B

    Converting ISO date format to MS_readable

    I get data that uses a date format (YYYYMMDD) [no hyphens/slashes]. There is no conversion tool (that I can see) for getting this into something that MS-Excel and Access can recognize.

Some videos you may like

This Week's Hot Topics

Top