Hi I am trying to create a time sheet.

Where column A is the date (ddd, dd,mmm,yyyy) in column B I would like it to
enter the time 9:15 (hh:mm) if the day is Monday, Tuesday, Wednesday or
Thursday and then enter 0:00 if it is Friday, Saturday or Sunday.

Is this at all possible? If so how would I go about working this out?

Any help appreciated.

Many thanks

JayM

Hi Jay,

Try the following formula and drag down.

=IF(OR(WEEKDAY(A1,2)=1,WEEKDAY(A1,2)=2,WEEKDAY(A1, 2)=3,WEEKDAY(A1,2)=4),TIMEVALUE("09:15"),TIMEVALUE ("00:00"))

and give the resulting cells a custom format of hh:mm

Dave

"JayM" wrote:

Hi I am trying to create a time sheet.

Where column A is the date (ddd, dd,mmm,yyyy) in column B I would like it to
enter the time 9:15 (hh:mm) if the day is Monday, Tuesday, Wednesday or
Thursday and then enter 0:00 if it is Friday, Saturday or Sunday.

Is this at all possible? If so how would I go about working this out?

Any help appreciated.

Many thanks

JayM

Thank you so much that works perfectly.

JayM

"Dave Curtis" wrote:

Hi Jay,

Try the following formula and drag down.

=IF(OR(WEEKDAY(A1,2)=1,WEEKDAY(A1,2)=2,WEEKDAY(A1, 2)=3,WEEKDAY(A1,2)=4),TIMEVALUE("09:15"),TIMEVALUE ("00:00"))

and give the resulting cells a custom format of hh:mm

Dave

"JayM" wrote:

Hi I am trying to create a time sheet.

Where column A is the date (ddd, dd,mmm,yyyy) in column B I would like it to
enter the time 9:15 (hh:mm) if the day is Monday, Tuesday, Wednesday or
Thursday and then enter 0:00 if it is Friday, Saturday or Sunday.

Is this at all possible? If so how would I go about working this out?

Any help appreciated.

Many thanks

JayM

Hi,

Here is a shorter approach:

=--IF(MOD(A1-2,7)3,"0:00","9:15")

then format the cells as time.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"JayM" wrote:

Hi I am trying to create a time sheet.

Where column A is the date (ddd, dd,mmm,yyyy) in column B I would like it to
enter the time 9:15 (hh:mm) if the day is Monday, Tuesday, Wednesday or
Thursday and then enter 0:00 if it is Friday, Saturday or Sunday.

Is this at all possible? If so how would I go about working this out?

Any help appreciated.

Many thanks

JayM

Even shorter...

=(WEEKDAY(A1,2)<5)*"9:15"


"Shane Devenshire" wrote:

Hi,

Here is a shorter approach:

=--IF(MOD(A1-2,7)3,"0:00","9:15")

then format the cells as time.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"JayM" wrote:

Hi I am trying to create a time sheet.

Where column A is the date (ddd, dd,mmm,yyyy) in column B I would like it to
enter the time 9:15 (hh:mm) if the day is Monday, Tuesday, Wednesday or
Thursday and then enter 0:00 if it is Friday, Saturday or Sunday.

Is this at all possible? If so how would I go about working this out?

Any help appreciated.

Many thanks

JayM