Day count funtion

%3CLINGO-SUB%20id%3D%22lingo-sub-3363036%22%20slang%3D%22en-US%22%3EDay%20count%20funtion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3363036%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20wondering%20how%20i%20write%20a%20command%20that%20will%20calculate%20the%20number%20of%20days%20from%20a%20given%20date%20and%20time.%20Example%20-%20I%20have%20a%20cell%20containing%2002%2F04%2F2022%2014%3A13%3A47%20.%20I%20wish%20to%20calculate%20the%20number%20of%20full%20days%20that%20has%20lapsed%20since%20that%20date%20and%20time.%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EBryan%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3363036%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3363241%22%20slang%3D%22en-US%22%3ERe%3A%20Day%20count%20funtion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3363241%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1388029%22%20target%3D%22_blank%22%3E%40Bryantotalis%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20the%20date%2Ftime%20is%20in%20A2.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20number%20of%2024-hour%20periods%20since%20that%20date%2Ftime%20is%20%3DINT(NOW()-A2)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3363237%22%20slang%3D%22en-US%22%3ERe%3A%20Day%20count%20funtion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3363237%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1388029%22%20target%3D%22_blank%22%3E%40Bryantotalis%3C%2FA%3E%26nbsp%3BLet's%20say%20that%20date%2Ftime%20is%20in%20A1%2C%20the%20following%20formula%20will%20calculate%26nbsp%3B%20the%20number%20of%20days%20(in%20decimals)%20lapsed%20till%20now.%20Round%20it%20to%20your%20liking%20and%20format%20the%20cell%20as%20General%20or%20Number%20as%20it%20will%20spit%20out%20a%20date%2Ftime%20value.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D(NOW()-A1)%3C%2FSTRONG%3E%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-3363250%22%20slang%3D%22en-US%22%3ERe%3A%20Day%20count%20funtion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3363250%22%20slang%3D%22en-US%22%3EI%20tried%20that%20with%2002%2F05%2F2022%2014%3A13%3A47%20and%20it%20gave%20me%20an%20answer%20of%2044693.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3363255%22%20slang%3D%22en-US%22%3ERe%3A%20Day%20count%20funtion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3363255%22%20slang%3D%22en-US%22%3EI%20tried%20that%20with%2002%2F05%2F2022%2014%3A13%3A47%20and%20it%20gave%20me%20an%20answer%20of%2044693.%20My%20existing%20cell%20is%20set%20as%20general.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3363295%22%20slang%3D%22en-US%22%3ERe%3A%20Day%20count%20funtion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3363295%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1388029%22%20target%3D%22_blank%22%3E%40Bryantotalis%3C%2FA%3E%26nbsp%3BThat%20number%20represents%20todays%20date%2C%20which%20would%20be%20the%20result%20of%20%3DNOW()-0%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20exactly%20did%20you%20enter%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3363337%22%20slang%3D%22en-US%22%3ERe%3A%20Day%20count%20funtion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3363337%22%20slang%3D%22en-US%22%3EI%20have%20a%20cell%20with%2002%2F05%2F2022%2014%3A13%3A47%20in%20it.%20I%20then%20entered%20%3DINT(NOW()-G1)%20which%20gave%20me%2044693%3CBR%20%2F%3E%3CBR%20%2F%3EI%20then%20tried%20%3D(NOW()-G1)%20which%20gave%20me%2044693.40%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3363372%22%20slang%3D%22en-US%22%3ERe%3A%20Day%20count%20funtion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3363372%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1388029%22%20target%3D%22_blank%22%3E%40Bryantotalis%3C%2FA%3E%26nbsp%3BWhatever%20you%20have%20in%20G1%2C%20it%20represents%20zero.%20Can%20you%20upload%20a%20screenshot%20or%20share%20the%20file%20via%20OneDerive%2C%20Dropbox%20etc.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3363411%22%20slang%3D%22en-US%22%3ERe%3A%20Day%20count%20funtion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3363411%22%20slang%3D%22en-US%22%3EGot%20it.%20Thanks%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi, 

Just wondering how i write a command that will calculate the number of days from a given date and time. Example - I have a cell containing 02/04/2022 14:13:47 . I wish to calculate the number of full days that has lapsed since that date and time.

Thanks,

Bryan

8 Replies

@Bryantotalis Let's say that date/time is in A1, the following formula will calculate  the number of days (in decimals) lapsed till now. Round it to your liking and format the cell as General or Number as it will spit out a date/time value.

 

=(NOW()-A1)

 

 

@Bryantotalis 

Let's say the date/time is in A2.

 

The number of 24-hour periods since that date/time is =INT(NOW()-A2)

I tried that with 02/05/2022 14:13:47 and it gave me an answer of 44693.
I tried that with 02/05/2022 14:13:47 and it gave me an answer of 44693. My existing cell is set as general.

@Bryantotalis That number represents todays date, which would be the result of =NOW()-0

 

What exactly did you enter? 

I have a cell with 02/05/2022 14:13:47 in it. I then entered =INT(NOW()-G1) which gave me 44693

I then tried =(NOW()-G1) which gave me 44693.40

@Bryantotalis Whatever you have in G1, it represents zero. Can you upload a screenshot or share the file via OneDerive, Dropbox etc.

Got it. Thanks