Help for Date counting.
Hi to all
I want to count the total number of date Example:- 26/05/1973 then total of day ie 2+6 =8 than total of all ie 2+6+5+1+9+7+3=33 ie=6 how can I do this when I just enter 26-05-1973 it should give 8in one column & 6 in other column. Thanks ****al |
Quote:
Hmm using the TEXT function you should be able to split it out using MID assuming the date is in cell A1 try total for the day =VALUE(LEFT(TEXT(A1,"dd"),1))+VALUE(MID(TEXT(A1,"d d"),2,1)) total of all =VALUE(LEFT(VALUE(LEFT(TEXT(A1,"ddmmyyyy"),1))+VAL UE(MID(TEXT(A1,"ddmmyyyy"),2,1))+VALUE(MID(TEXT(A1 ,"ddmmyyyy"),3,1))+VALUE(MID(TEXT(A1,"ddmmyyyy"),4 ,1))+VALUE(MID(TEXT(A1,"ddmmyyyy"),5,1))+VALUE(MID (TEXT(A1,"ddmmyyyy"),6,1))+VALUE(MID(TEXT(A1,"ddmm yyyy"),7,1))+VALUE(MID(TEXT(A1,"ddmmyyyy"),8,1)),1 ))+VALUE(MID(VALUE(LEFT(TEXT(A1,"ddmmyyyy"),1))+VA LUE(MID(TEXT(A1,"ddmmyyyy"),2,1))+VALUE(MID(TEXT(A 1,"ddmmyyyy"),3,1))+VALUE(MID(TEXT(A1,"ddmmyyyy"), 4,1))+VALUE(MID(TEXT(A1,"ddmmyyyy"),5,1))+VALUE(MI D(TEXT(A1,"ddmmyyyy"),6,1))+VALUE(MID(TEXT(A1,"ddm myyyy"),7,1))+VALUE(MID(TEXT(A1,"ddmmyyyy"),8,1)), 2,1)) a bit scruffy though I grant you. Dates are always a bit of a problem. |
All times are GMT +1. The time now is 01:55 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com