time formulate

[arslan]

correction for my problem...

A B C D
E
1 date working hours work.hors yor work.hours for work.hours
last 7 days last 30 days
for last year
2 12.20.05 08:15
3 01.01.06 08:00
4 03.25.06 07:55
5 04.15.06 08:45
How can i formulate each cell on column C,D, and E?

 

[Domenic]

For the total hours in the last 7 days, try...

=SUMPRODUCT(--(SUBSTITUTE(A2:A5,".","/")+0>TODAY()-7),B2:B5)

....and format the cell as follows...

Format > Cells > Number > Custom > Type: [h]:mm

If you change the format so that Column A contains true dates, for
example...

12/20/05
4/11/06
4/25/06
4/15/06

....you could use the following formula instead...

=SUMPRODUCT(--(A2:A5>TODAY()-7),B2:B5)

Hope this helps!

 

[arslan]

thanks, Domenic...

"Domenic":

For the total hours in the last 7 days, try...

=SUMPRODUCT(--(SUBSTITUTE(A2:A5,".","/")+0>TODAY()-7),B2:B5)

....and format the cell as follows...

Format > Cells > Number > Custom > Type: [h]:mm

If you change the format so that Column A contains true dates, for
example...

12/20/05
4/11/06
4/25/06
4/15/06

....you could use the following formula instead...

=SUMPRODUCT(--(A2:A5>TODAY()-7),B2:B5)

Hope this helps!

correction for my problem...

A B C D
E
1 date working hours work.hors yor work.hours for work.hours
last 7 days last 30 days
for last year
2 12.20.05 08:15
3 01.01.06 08:00
4 03.25.06 07:55
5 04.15.06 08:45
How can i formulate each cell on column C,D, and E?

 

[Domenic]

thanks, Domenic...

You're very welcome! Actually, if Column A contains true dates, you
could simply use...

=SUMIF(A2:A5,">"&TODAY()-7,B2:B5)

Hope this helps!