formula required for day wise when leave is more than 1 day

[m9vukyem]

Dear sir,

Please provide the formula for my leaves register is from to to date now i want to date wise segregation required

 

[GraH - Guido]

Here is a Power Query solution.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Serial No", Int64.Type}, {"Emp Code", Int64.Type}, {"Emp Name", type text}, {"Leave from", type date}, {"Leave to", type date}, {"No of Leaves", Int64.Type}, {"Type of Leave", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "List", each List.Dates([Leave from],[No of Leaves],#duration(1,0,0,0))),
    #"Expanded List" = Table.ExpandListColumn(#"Added Custom", "List"),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded List",{"Emp Code", "Emp Name", "Type of Leave", "List"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Other Columns", "SN", 1, 1, Int64.Type),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Index",{{"List", type date}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"SN", "Emp Code", "Emp Name", "List", "Type of Leave"})
in
    #"Reordered Columns"

 

[m9vukyem]

Thank you for your kind help but i need a formula ,using excel 2016 version sir

 

[vletm]

Where do You need F-column values?
... from D-column to E-column values.
Here other kind of sample solution ...

 

[AlanSidman]

FYI: How to employ Power Query Mcode supplied by GraH - Guido

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

The Complete Guide to Installing Power Query - Excel Campus

Learn what version you need to get Power Query and instructions on how to install this free add-in from Microsoft.

www.excelcampus.com

- Follow this link for an introduction to Power Query functionality.

Power Query Overview: An Introduction to Excel's Most Powerful Data Tool - Excel Campus

Learn how this FREE Excel add-in from Microsoft called Power Query will help transform your data into a format that is ready for pivot tables, reports and more.

www.excelcampus.com

- Follow this link for a video which demonstrates how to use Power Query code provided.

Power Query - How To Paste Code (video) - Excel Solutions

Power Query is great at solving data transformation and consolidation issues. But there are still loads of Excel users who have never used Power Query yet – and sometimes when they’re given some Power Query code to answer their question, they don’t know what to do with it. Here’s a short video...

excel.solutions

 

[m9vukyem]

Thank you for giving information about power query but I Need a formula sir. i have no knowledge about powe query

 

[m9vukyem]

Where do You need F-column values?
... from D-column to E-column values.
Here other kind of sample solution ...

Thank you sir. Please provide a formula

 

[shili12]

Thank you sir. Please provide a formula

you will have to be content with a power query solution as we all are, regretfully.

 

[p45cal]

In the attached, a udf called blah.
This formula needs to be array-entered (commit the formula to multiple cells at once using Ctrl+Shift+Enter rather than the usual plain Enter).
The area you need to select needs to be 5 cells wide, but the number of rows will vary according to your data; if you choose too few rows the result will show how many rows you need to use. See cells O12 to Q14 in the attached.



The correct number of rows was selected when the formula was array-entered into J12:N19 of the attached.
I leave you to add the headers to the result and to format column 4 as a date.



Asking for a formula using built-in functions to do this is akin to asking a courier to deliver a package from London to Edinburgh insisting he uses a penny-farthing bicycle rather than a train or a car (and not compensating him for it either).