SOLVED
Home

Date calculation formula needed for daily pickup

%3CLINGO-SUB%20id%3D%22lingo-sub-714068%22%20slang%3D%22en-US%22%3EDate%20calculation%20formula%20needed%20for%20daily%20pickup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-714068%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Experts%2C%3CBR%20%2F%3EI%20am%20looking%20for%20a%20formula%20that%20calculates%20daily%20numbers%20from%20check-in%20and%20check-out%20dates.%20I%20am%20aware%20of%20the%20basic%20formula%20that%20calculates%20the%20total%20number%20of%20days%20between%20two%20dates%20but%20what%20I%20need%20is%20a%20formula%20that%20breaks%20down%20the%20total%20number%20of%20days%20day%20by%20day.%3CBR%20%2F%3EFor%20example%3A%20check-in%3A%205%2F1%2F10%20Check-out%3A%205%2F5%2F19%20equals%204%20days.%20Now%20I%20need%20to%20break%20down%20the%204%20days%20by%20the%20day%20like%20this%3A%3CBR%20%2F%3E5%2F1%2F19%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%205%2F2%2F19%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%205%2F3%2F19%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%205%2F4%2F19%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%201%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201%3CBR%20%2F%3EIs%20there%20a%20formula%20that%20pulls%20from%20the%20check-in%20and%20check-out%20columns%20and%20automatically%20calculates%20the%20daily%20breakdown%20as%20shown%20above%20in%20separate%20columns%3F%20Attached%20is%20the%20file%20with%20process%20I%20need%20help%20with%20.%3CBR%20%2F%3EThank%20you%20for%20your%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-714068%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-714090%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20calculation%20formula%20needed%20for%20daily%20pickup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-714090%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364596%22%20target%3D%22_blank%22%3E%40excel101%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20use%20in%20E2%3C%2FP%3E%0A%3CPRE%3E%3DIF((E%241%26gt%3B%3D%24A2)*(E%241%26lt%3B%3D%24B2)%2C1%2C%22%22)%3C%2FPRE%3E%0A%3CP%3Eand%20drag%20it%20to%20entire%20range%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-714099%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20calculation%20formula%20needed%20for%20daily%20pickup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-714099%22%20slang%3D%22en-US%22%3EIt%20appears%20that%20the%20check-in%20date%20is%20included%20in%2C%20while%20the%20check-out%20date%20is%20excluded%20from%2C%20the%20calculation%20of%20total%20nights.%20Thus%2C%20the%20formula%20in%20E2%2C%20copied%20down%20rows%20and%20across%20columns%2C%20is%3A%3CBR%20%2F%3E%3DIF((E%241%26gt%3B%3D%24A2)*(E%241%26lt%3B%24B2)%2C%3CBR%20%2F%3E1%2C%E2%80%9D%E2%80%9D)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-714362%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20calculation%20formula%20needed%20for%20daily%20pickup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-714362%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3EThank%20you%20so%20much!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-714364%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20calculation%20formula%20needed%20for%20daily%20pickup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-714364%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3EThank%20you%20so%20much!%20It%20worked!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-714519%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20calculation%20formula%20needed%20for%20daily%20pickup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-714519%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3EHow%20can%20I%20use%20the%20formula%20you%20provided%20with%20a%20different%20date%20range%3F%20I%20tried%20to%20do%20it%20with%20a%20different%20date%20range%20but%20it%20is%20not%20working.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20again%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-715037%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20calculation%20formula%20needed%20for%20daily%20pickup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-715037%22%20slang%3D%22en-US%22%3EIt%20depends%20upon%20the%20layout%20of%20your%20data.%20The%20formula%20always%20conforms%20to%20such%20layout%20but%20the%20logic%20would%20remain%20the%20same.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-717080%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20calculation%20formula%20needed%20for%20daily%20pickup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-717080%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%20Thank%20you!%20Can%20you%20elaborate%20a%20bit%20further%20please.%20I%20have%20used%20the%20same%20formula%20but%20it%20just%20not%20working.%20What%20do%20you%20mean%20by%20%22data%20layout%22%20exactly%3F%20Do%20mind%20looking%20at%20the%20attachment%20with%20the%20new%20set%20of%20dates%20and%20the%20formula%20please%3F%20Thank%20you%20again.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-717738%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20calculation%20formula%20needed%20for%20daily%20pickup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-717738%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364596%22%20target%3D%22_blank%22%3E%40excel101%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFormula%20works%20(attached)%2C%20what%20exactly%20is%20wrong%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-718156%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20calculation%20formula%20needed%20for%20daily%20pickup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-718156%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3EThank%20you!%20One%20thing%20I%20noticed%20is%20that%20when%20I%20copied%20the%20dates%20and%20formula%20that%20you%20sent%20back%20and%20pasted%20into%20a%20new%20spreadsheet%2C%20the%20formula%20did%20not%20work.%20But%20when%20I%20re-typed%20one%20of%20the%20check-in%2Fcheck-out%20dates%20the%20formula%20worked%20all%20of%20a%20sudden.%20It's%20like%20the%20the%20dates%20were%20not%20recognized%20until%20I%20re-typed%20them%20manually.%20Do%20you%20know%20what%20that%20could%20be%3F%20Thanks%20again!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-718514%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20calculation%20formula%20needed%20for%20daily%20pickup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-718514%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364596%22%20target%3D%22_blank%22%3E%40excel101%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20you%20didn't%20fix%20the%20row%20with%20absolute%20reference%20or%20like.%20Most%20probably%20wrong%20combination%20of%20absolute%2Frelative%20references%20in%20pasted%20formulas.%3C%2FP%3E%3C%2FLINGO-BODY%3E
excel101
Occasional Contributor

Hello Experts,
I am looking for a formula that calculates daily numbers from check-in and check-out dates. I am aware of the basic formula that calculates the total number of days between two dates but what I need is a formula that breaks down the total number of days day by day.
For example: check-in: 5/1/10 Check-out: 5/5/19 equals 4 days. Now I need to break down the 4 days by the day like this:
5/1/19     5/2/19      5/3/19       5/4/19
    1              1               1               1
Is there a formula that pulls from the check-in and check-out columns and automatically calculates the daily breakdown as shown above in separate columns? Attached is the file with process I need help with .
Thank you for your help!

10 Replies

@excel101 

You may use in E2

=IF((E$1>=$A2)*(E$1<=$B2),1,"")

and drag it to entire range

 

It appears that the check-in date is included in, while the check-out date is excluded from, the calculation of total nights. Thus, the formula in E2, copied down rows and across columns, is:
=IF((E$1>=$A2)*(E$1<$B2),
1,””)
Solution

@Sergei BaklanThank you so much!

@TwifooThank you so much! It worked!

@TwifooHow can I use the formula you provided with a different date range? I tried to do it with a different date range but it is not working.

 

Thank you again

It depends upon the layout of your data. The formula always conforms to such layout but the logic would remain the same.

@Twifoo @Twifoo Thank you! Can you elaborate a bit further please. I have used the same formula but it just not working. What do you mean by "data layout" exactly? Do mind looking at the attachment with the new set of dates and the formula please? Thank you again.

@excel101 

Formula works (attached), what exactly is wrong?

@Sergei BaklanThank you! One thing I noticed is that when I copied the dates and formula that you sent back and pasted into a new spreadsheet, the formula did not work. But when I re-typed one of the check-in/check-out dates the formula worked all of a sudden. It's like the the dates were not recognized until I re-typed them manually. Do you know what that could be? Thanks again!

 

 

@excel101 

 

Perhaps you didn't fix the row with absolute reference or like. Most probably wrong combination of absolute/relative references in pasted formulas.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies